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!
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 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 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.
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).