It Concatenates two or more values. Return type
is string.
Advantage of this is it can concatenate same or
different data types.
Example:
Declare @ProdName as Varchar(20);
Declare @Price as Money;
set @ProdName='Book';
set @Price=20.00;
We can concatenate in two different way.
First Method:
select @ProdName+' costs' as Prods
OutPut:
Prods
Book costs
But try to add @price it will throw error
because of different data types.
select @ProdName+' costs' +@Price as outputs
Msg 235, Level 16, State 0,
Line 6
Cannot convert a char value to
money. The char value has incorrect syntax.
Second Method:
Declare @ProdName as Varchar(20);
Declare @Price as Money;
set @ProdName='Book';
set @Price=20.00;
select CONCAT(@ProdName,' Costs ',@price) as Prods;
OutPut:
Prods
Book Costs 20.00
Here is few more examples:
Returns a string that is the result of two or more strings joined together. CONCAT accepts two or more arguments.
SELECT CONCAT('This', ' is', ' my', ' string') -- returns 'This is my string'
Note: Unlike concatenating strings using the string concatenation operator (+), when passing a null value to the concat function it will implicitly convert it to an empty string:
SELECT CONCAT('This', NULL, ' is', ' my', ' string'), -- returns 'This is my string'
'This' + NULL + ' is' + ' my' + ' string' -- returns NULL.
Also arguments of a non-string type will be implicitly converted to a string:
SELECT CONCAT('This', ' is my ', 3, 'rd string') -- returns 'This is my 3rd string'
Non-string type variables will also be converted to string format, no need to manually covert or cast it to string:
DECLARE @Age INT=23;
SELECT CONCAT('Ram is ', @Age,' years old'); -- returns 'Ram is 23 years old'
