February 23, 2010 at 3:10 pm
I hope this is the right place to ask this. If not I apologize and would request guidance.
I have this sql statement
SELECT [Fname]+' '+[middle]+' '+[Lname] AS fullname
It works just fine and produces John R Smith in the fullname column. However, I do allow nulls for the middle value and when the middle initial is left blank, I don't return any value at all, like the whole record is blank.
Is my statement flawed or do I need something different in the middle column than just allow nulls? Unfortunately requiring an initial is no an option.
Thanks very much for any advise.
February 23, 2010 at 3:18 pm
One option is:
SELECT [Fname] + ' ' + ISNULL([middle] + ' ', '') + [Lname] AS fullname
February 23, 2010 at 3:23 pm
Look at the ISNULL function.
ISNULL ( check_expression , replacement_value )
check_expression
Is the expression to be checked for NULL. check_expression can be of any type.
replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion
SELECT [Fname]+' '+ISNULL([middle],' ')+' '+[Lname] AS fullname
that is replace the null value with a blank - or use a CASE statement if the blank makes 2 blanks between first and last name and that is not desirable.
February 23, 2010 at 3:43 pm
Also, just as another avenue, you could use: SET CONCAT_NULL_YIELDS_NULL OFF
That causes concatenation to return a value even when it has a null value as one of the pieces being concatenated.
February 26, 2010 at 7:22 am
Thanks very much to all who responded. This solved my problem. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply