Help with simple IsNULL query syntax

What is wrong with this syntax?

I am trying to add a '' if the value of the field is NULL

SELECT  ISNULL (users.FirstNm, '') AS FirstNm + ' ' + ISNULL (users.MiddleNm, '') AS MiddleNm + ' ' ISNULL (users.LastNm, '') AS LastNm    AS fullname 
    FROM   dbo.Cases
           INNER JOIN Users ON Users.UserId = Cases.Signatoryid
           LEFT JOIN Addresses AS c ON Users.Docstatusstat = c.Id

Open in new window


Getting an error saying there is a syntax error by the +  sign.
LVL 1
AleksAsked:
Who is Participating?
 
Russ SuterCommented:
Actually, you can simplify a bit more by using the CONCAT() function.
SELECT  CONCAT(ISNULL (users.FirstNm, ''), ' ', ISNULL (users.MiddleNm, ''), ' ', ISNULL (users.LastNm, ''))  AS fullname 
    FROM   dbo.Cases
           INNER JOIN Users ON Users.UserId = Cases.Signatoryid
           LEFT JOIN Addresses AS c ON Users.Docstatusstat = c.Id

Open in new window

0
 
Russ SuterCommented:
You cannot use aliases like that. You can only alias the entire column result. Try it like this:
SELECT  ISNULL (users.FirstNm, '') + ' ' + ISNULL (users.MiddleNm, '') + ' ' ISNULL (users.LastNm, '')  AS fullname 
    FROM   dbo.Cases
           INNER JOIN Users ON Users.UserId = Cases.Signatoryid
           LEFT JOIN Addresses AS c ON Users.Docstatusstat = c.Id

Open in new window

0
 
AleksAuthor Commented:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'users'.
0
 
AleksAuthor Commented:
This section option worked. The first one has a syntax error.
0
 
Russ SuterCommented:
Yep, I missed an extra '+' near the end of the concatenation. DOH! Actually, looking at it now, so did you. ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.