question

aRookieBIdev avatar image
aRookieBIdev asked

Sql query to handle null

Hi All , I need to concatenate three fields with space inbetween, example select firstname + ' ' + middlename+' ' + lastname In case if one of the field is null in that case i should not use the extra space. Kindly suggest me an example. Thanks, Kannan
sql-server-2008query
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
You could use CONCAT_NULL_YIELDS_NULL and COALESCE. SET CONCAT_NULL_YIELDS_NULL ON SELECT COALESCE(firstname + ' ','') + COALESCE(middlename + ' ','') + COALESCE(lastname ,'')
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.