How to replace NULL with Empty String in SQL SERVER? ISNULL and COALESCE Examples

来源:转载

We often need to replace NULL values with empty String or blank in SQL e.g. while concatenating String. In SQL SERVER, when you concatenate a NULL String with another non-null String the result is NULL, which means you lose the information you already have. To prevent this, you can replace NULL with empty String while concatenating . There are two ways to replace NULL with blank values in SQL SERVER, function ISNULL() and COALESCE(). Both functions replace the value you provide when the argument is NULL e.g. ISNULL(column, '') will return empty String if the column value is NULL. Similarly COALESCE(column, '') will also return blank if the column is NULL. The only difference between them is that ISNULL() is SQL SERVER specific but COALESCE is the standard way and supported by all major database like MySQL, Oracle and PostgreSQL. Another difference between them is that you can provide multiple optional values to COALESCE() e.g. COALESCE(column, column2, '') , so if the column is null then it will use column2 and if that is also null then it will use empty String. For SQL SERVER and T-SQL beginners, I also recommend reading Microsoft SQL SERVER 2012 T-SQL Fundamentals , one of the best books to learn the T-SQL concept.

Replacing NULL with blank in SQL SERVER - ISNULL() Example

Let's first see, how to use ISNULL() to replace NULL String to empty String in SQL SERVER. In order to understand the problem and solution better, let's create a sample database with some values.

IF OBJECT_ID( 'tempdb..#People' ) IS NOT NULLDROP TABLE #People;CREATE TABLE #People (first_name varchar(30), last_name varchar(30));INSERT INTO #People VALUES ('Joe','Root');INSERT INTO #People VALUES ('Mary', NULL);INSERT INTO #People VALUES (NULL, 'Broad');-- cleanup-- DROP TABLE #People

Now let's display the first name, last name and full name from #People table, where full name is nothing but concatenation of first and last name. Here is our SQL query:

SELECT first_name, last_name, first_name + last_name AS full_name FROM #Peoplefirst_name last_name full_nameJoe Root JoeRootMary NULL NULLNULL Broad NULL

You can see that full_name is NULL for second and third record because for them either first_name or last_name is NULL. In order to avoid that and to replace the NULL with empty String, let's use ISNULL() method in our SQL query:

SELECT first_name, last_name, ISNULL(first_name,'') + ISNULL(last_name,'') as full_name FROM #Peoplefirst_name last_name full_nameJoe Root JoeRootMary NULL MaryNULL Broad Broad

You can see that even though one of the joining column is NULL but full_name is not NULL anymore because ISNULL() is replacing NULL values with blank.

Using COALESCE() to replace NULL with empty String in SQL SERVER

In earlier example, you have learned how to use ISNULL() to replace NULL values with blank inSQL SREVER, let's see how can we use COALESCE() to do the same. Remember, COALESCE() is a standard function and whenever you can use COALESCE() you should be using it. In this example, you don't need to do anything, just replace ISNULL() with COALESCE() and you are done, as shown in following SQL query:

SELECT first_name, last_name, COALESCE(first_name,'') + COALESCE(last_name,'') as full_name FROM #Peoplefirst_name last_name full_nameJoe Root JoeRootMary NULL MaryNULL Broad Broad

Let me show you another use of COALESECE() function while we are using it. You can use COALESCE() to instruct using value of another column if the target column is NULL and if that is also null then use third column and so on. You can use this technique to provide sophisticated default values in your reports. For example, in this scenario, let's display value of last_name if first_name is NULL and value of first_name if last_name is NULL in the report. Follwing SQL query uses COALESCE to do that:

SELECT COALESCE(first_name,last_name, '') as first_name,COALESCE(last_name, first_name,'') as last_name,COALESCE(first_name,'') + COALESCE(last_name,'') as full_nameFROM #Peoplefirst_name last_name full_nameJoe Root JoeRootMary Mary MaryBroad Broad Broad

Here is the screenshot of SQL queries from Microsoft SQL SERVER 2014 database to give you full view:

That's all about how to replace NULL with empty String or blank in SQL SERVER . You can use ISNULL() or COALESCE() to replace NULL with blanks. It's particularly important to use these function while concatenating String in SQL SERVER because one NULL can turn all information into NULL. Btw, you can also use CONCAT() instead of + operator to avoid NULL, this function returns the value of non null argument if other argument is NULL. Between ISNULL() and COALESCE() , use ISNULL() if you know for sure that your code will run on SQL SERVER but COALESCE() is better becuase its standard and you can use it to replace NULL with empty String in any database e.g. Oracle, MySQL and PostgreSQL.

Further Reading

Querying Microsoft SQL SERVER 2012 Training Kit for Exam 70-461 ( see here )

Microsoft SQL SERVER 2012 T-SQL Fundamentals ( check here )



分享给朋友:
您可能感兴趣的文章:
随机阅读: