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