SQL Server Strings Concatenation

 in SQL Server  tagged type / data / convert / cast / string / IIF / concatenation / case / Coalesce / precedence by

Numbers and Strings

If you want to concatenate a number to a string you need to know how SQL Server works behind the scenes. For example if you want to concatenate a number to a sting, as in 345 + ‘ is the amount’, SQL Server will always try to convert a string to a number in the presence of a number. Therefore you always need to convert the number to a string before concatenating them. 

select cast(453 as varchar(3)) + ' is the amount'  -- 453 is the amount
select 'The amount is ' + cast(453 as varchar(3)) -- The amount is 453
select convert(varchar(3),453) + ' is the amount' -- 453 is the amount
select 'The amount is ' + convert(varchar(3),453)  -- The amount is 453
select cast(453 as varchar(2)) + ' is the amount' -- * is the amount - varchar is not big enough!

IIF

Concatenating strings is not difficult but there are some issues to consider when one of those string might be NULL. One way to deal with this is to use IIF, as shown below. The best way is to use CONCAT (shown at the bottom of the post). IIF and CASE have better applications outside of string concatenation. 

declare @FirstName nvarchar(20) = 'Bill'
declare @MiddleName nvarchar(20) = 'Bob'
declare @LastName nvarchar(20) = 'Thorton'
select @FirstName + ' ' + @MiddleName + ' ' + @LastName as FullName
SET @MiddleName = NULL
-- SET @MiddleName = 'Bob';
-- assume that firstname and lastname can NEVER be NULL.
select @FirstName + ' ' + @MiddleName + ' ' + @LastName as FullName
select @FirstName + ' ' + iif(@MiddleName IS NULL, '', @MiddleName + ' ') + @LastName as FullName

Here are the results.

That demonstrates one way of handling the possibility of NULL. Another way is to use CASE.

CASE

Case is like if. You can have a whole list of conditions. If A then do this, if B do that,if C do another thing, otherwise do yet another thing. Case will do the first condition that is true. Here is just the SELECT statement using CASE and the data from the previous listing. 

select @FirstName + CASE WHEN @MiddleName IS NULL THEN '' ELSE ' ' + @MiddleName END + ' ' + @LastName

We can take a look at SQL Server Books Online to see an example of CASE. Here is the code from that website with an extra select statement at the end that I added for additional clarity. 

USE AdventureWorks2014;  
GO  
SELECT   ProductNumber, Category =  
      CASE ProductLine  
         WHEN 'R' THEN 'Road'  
         WHEN 'M' THEN 'Mountain'  
         WHEN 'T' THEN 'Touring'  
         WHEN 'S' THEN 'Other sale items'  
         ELSE 'Not for sale'  
      END,  
   Name  
FROM Production.Product  
ORDER BY ProductNumber;  
GO
select ProductNumber, ProductLine, Name
from  Production.Product 
ORDER BY ProductNumber;

Here are part of the results.

Coalesce

Coalesce takes any number of arguments from 1 to n. Having only one argument however is not practical. Coalesce takes each argument in turn starting from the left hand side. It is looking for the first non-NULL expression it finds, and applies that one. If the first one is NULL, coalesce goes to the next one. If that one is NULL it goes to the next, and so on until it finds a non-NULL expression. Remember that NULL means “Don’t know”. An empty string is not NULL. It is definite. It is an empty string. Empty strings are not NULL. 

select @FirstName + coalesce(' ' + @MiddleName, '') + ' ' + @LastName as FullName

The first argument is space plus @MiddleName. If @MiddleName is NULL, then space plus @MiddleName is NULL and Coalesce moves on to the next argument, which is an empty string, which is not null and therefore it is applied.

CONCAT

This may be the best way. We avoid the complexity of the plus signs. Concat takes as many arguments as you like and if any single argument is NULL, it simply disregards it. 

select CONCAT(@FirstName, ' ' + @MiddleName, ' ', @LastName) as FullName

Joining a String to a Number

Strings are enclosed in single quotation marks and are coloured red in the SSMS. When you use plus to concatenate a number to a string, SSMS will attempt to convert the string to a number, not the number to a string. Why? Data type precedence. Right at the bottom of the hierarch is all of the strings. Lower precedence data types are converted to higher ones. Implicit conversions are not always what you want. You can use cast() or convert() to explicitly convert.

You can use convert, cast or format for example. Here are some examples. 

select 'My salary is: ' + convert(varchar(20), 2345.6)
select 'My salary is: ' + format(234.5,'C')
select 'My salary is: ' + format(234.5,'C','en-GB')
select 'My salary is: ' + format(1234.5,'C','fr-FR')

Here is the output in SSMS.

For a list of all of the ways to format you can go online to Standard Numeric Format Strings. The most important and frequently used ones are C, D, F and G. These are the same as .NET (C# VB).