Sunday, July 29, 2018

STRING FUNCTION: CONCAT()

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'

No comments:

Post a Comment