CONCAT String With TSQL


Joining strings together

We will declare three variables and set their values, then the various select statement will be use for joining the string and print the outcome.

declare @firstname as nvarchar(20)
declare @middlename as nvarchar(20)
declare @lastname as nvarchar(20)

set @firstname = 'Maxybyte'
set @middlename ='Technologies'
set @lastname = 'Program'

select @firstname + ' ' @middlename +  ' '  + @lastname as FullName

FullName 
Maxybyte Technologies Program

What happens when you are adding three string while just two strings are set? then you get a Null as answer.

declare @firstname as nvarchar(20)
declare @middlename as nvarchar(20)
declare @lastname as nvarchar(20)

set @firstname = 'Maxybyte'
set @lastname = 'Program'

select @firstname + '  ' iif(@middlename is null, '', '  '  + @middlename+  '  '  + @lastname as FullName

FullName
Maxybyte Program

The following select statement can also be used.
select @firstname + CASE WHEN @middlename IS  NULL THEN ''  ELSE '  '  + @middlename END +  '  '  + @lastname as FullName

FullName
Maxybyte Program

The following select statement can also be used.
select @firstname + coalesce('  '  + @middlename, ") +  '  ' + @lastname as FullName

FullName
Maxybyte Program

The best use case for the select statement to join two string together is the CONCAT method
select CONCAT(@firstname, '  '  +  @middlename,  '  ', @lastname as FullName
 


 

No comments:

Post a Comment

Note: only a member of this blog may post a comment.

New Post

Create Store procedures

Store procedure has some similarities with views in the sense that it allows encapsulation. i.e. to retrieve for further, later use a series...