Transact-SQL Concatenate String with Null
July 13th, 2006Today I was altering a computed column of one of the tables in our SQL 2005 database, but was hitting the problem that concatenating a string with a null value produces a null value. My table had a FirstName and a LastName column and I was trying to make a FullName column with a computed value of (FirstName + LastName). The problem I was hitting was that when the LastName column was null the whole equation was returning null, but I, of course, wanted an empty string. The key to solving the problem is using Transact-SQL’s IsNull() function which returns a predefined value if the first parameter is null.
Wrong:
SELECT ('Test String' + NULL)
----------------------------------------------------------
NULL
Right:
SELECT ( 'Test String' + ISNULL(NULL, '') )
----------------------------------------------------------
Test String
Hopefully if someone is Googling around looking for an answer to this problem (like I was a little bit ago) they will find this and it will make their day.
Your wish just came true. Google gave me this site, and it just made my day!
I’m just starting on converting from Access to SQL Server and I work by myself :0{
In Access it was just (Nz(fldvalue))
Another thankful Googler…
Hey, you’re great! I am having this kind of headache for so long and for every variable I need to use if..else clause to assign the variable a value if the @variable is null. I am googling and I got you as you wish! Keep up the good work!
In Access it was even simpler than Vic says - just use & instead of +
But that doesn’t work in SQL
Ow my god man! U are awesome. Just made my day.
I realized today that my string wasn’t showing up because of a NULL value in the string.
After googling a bit i found your site and the right solution!
Saved me loads of time, thank you.
God Bless you and Google!!
Thank you!
Why don’t MS put this kind of tips in their online help? … it is a mistery
Good lord, you REALLY just made my day. Was using Visual Studio 2005 and it took me a while to realize why when I added a new field to my concatenated string I kept getting nothing. HELLO? ADDING 1+0 does not equal 0!! It is not multiplication! But anyway, thanks for the solution to my troubles.
Thanx !
Good tips !