These newer approaches reduce typing and having to switch between adding a “+” and ‘ ‘ in various places. I think that the syntax with these new functions is easier to write and to read. SELECT TOP 5 Title, FirstName, MiddleName, LastName,ĬONCAT_WS(' ',Title,FirstName,MiddleName,LastName) as MailingName Like CONCAT(), CONCAT_WS() eliminates NULLS from the result set, leaving you with the non-null values. These column names are separated by commas in a list. The value used as the separator goes first inside the parentheses followed by the column names that are to be joined together. With this new function, you specify the separator you want to use when building the string. The great thing here is that there is no need to do an explicit cast when using CONCAT().
SELECT CAST(67 as CHAR(2)) + CAST(45 as CHAR(2)) + CAST(90 as CHAR(2)) AS ResultĬoncatenating numbers works in the same way.
What about concatenating numbers? CREATE DATABASE TestDB However, with CONCAT(), columns with NULL values simply have those values ignored. This makes some sense because NULL is “unknown” so you can’t really join an unknown value to other strings and get a predictable result. In the top result set, you can see that whenever a NULL occurs the resulting MailingName column is also NULL. Title + ' ' + FirstName + ' ' + MiddleName + ' ' + LastName as MailingNameĬONCAT(Title,' ',FirstName,' ',MiddleName,' ', LastName) as MailingName One disadvantage to this method is that if a value in the string to be concactenated is NULL then the result set returns a NULL. AS C ON CUST.Deliver圜ityID = C.CityID īefore SQL Server 2012 the “+” sign was used to combine strings together into whatever form you needed. (DeliveryAddressLine2 + ' ' + C.CityName + ' ' + DeliveryPostalCode) AS OlderConcatMethodįROM. SELECT CONCAT(DeliveryAddressLine2, ' ', C.CityName, ' ',DeliveryPostalCode) AS NewConcatMethod,ĬONCAT_WS(' ', DeliveryAddressLine2, C.CityName,DeliveryPostalCode) AS CONCAT_WSMethod, The third method is the traditional method of combining strings with the
The first example is the CONCAT() method from SQL Server 2012, followed by the CONCAT_WS() function, which is CONCAT With Separator. They are just different ways of doing the same thing. These all produce the same results because there are no NULL values.
#Cant use concat in sql server management studio 17 zip#
Another common usage might be for creating an address column that pulls together building number, street, city and zip code.īelow I have provided an example of the three common ways to concatenate strings to create an address. In SQL Server 2017 we get CONCAT_WS().Ī common usage of concatenation, or joining column values together in a string, is combining a FirstName and LastName column into a FullName column. SQL Server 2012 introduced the CONCAT() function. SQL Server concatenation methods have been enhanced in modern versions of SQL Server.