Back to list SQL Server How to Concatenate String and NULL Values in SQL Server Database: SQL Server Operators:CONCAT(),CONCAT_WS() Problem: You’d like to concatenate NULLs with string values from a different column in SQL Server. Example: Our database has a table named children with data in the following columns: id (primary key), first_name, middle_name, and last_name. idfirst_namemiddle_namelast_name 1LindaNULLJackson 2MaryAliceThomson 3NULLStevenNULL 4NULLNULLBrown We want to display the first name from one column, the middle name from the next column, and the last name from the last column as one string, even if one of the columns stores a NULL. Solution 2: We’ll use the CONCAT() function. Here’s the query you’d write: SELECT CONCAT(first_name, middle_name, last_name) AS name FROM children; Here is the result: name LindaJackson MaryAliceThomson Steven Brown Discussion: Use the CONCAT() function to concatenate string values from expressions or columns containing a NULL. This function takes a list of strings (or NULLs) and displays all these values in one string. There is no separator between the values, so the results (as in our example) may not be formatted as you’d expect. How can we correct this? Look at another CONCAT() query: Solution 2: Here’s another query option: SELECT CONCAT(first_name,' ' , middle_name, ' ',last_name) AS name FROM children; Here is the result: name Linda Jackson Mary Alice Thomson Steven Brown Now, apart from string values, this function also takes some spaces (placed between the concatenated values). This separates one part of the name from another. But as we see, this solution is also not ideal; full names without middle names have an extra space, while the records with only one name have two extra spaces. In this case, the CONCAT_WS() function is the better solution. Solution 3: The CONCAT_WS() function takes an additional first argument: a character that serves as a separator between strings. Here’s the query: SELECT CONCAT_WS(' ' , first_name, middle_name, last_name) AS name FROM children; And the result: name Linda Jackson Mary Alice Thomson Steven Brown This query displays the children’s full names, without any unneeded spaces. Recommended courses: SQL Basics in SQL Server SQL Practice Set Standard SQL Functions Recommended articles: 5 SQL Functions for Manipulating Strings 18 Useful Important SQL Functions to Learn ASAP See also: How to remove leading and/or trailing spaces of a string in T-SQL How to Change Date and Time Formats in T-SQL How to Extract or Convert Time Data from a String in SQL Server Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.