April 24, 2008 at 8:13 am
I am concatenating three fields into one: following is my sql
selectLast_Name+' '+First_Name+' '+Middle_Name as Name,
MGR_First_Name,
MGR_Last_Name
INTO EmployeeData
from EP_Data
and in the resulting table i see that some rows have null value in the Name column which i create by using sql above. I see that its happening wherever any of the three parts of the name have a null value. e.g. if middle_name was null then in the resulting set my name column will be null.
Why would the result set not have first name and last name. Why is it throughing out null for the whole value when only one part of it is null?
thanks
April 24, 2008 at 8:25 am
SELECTRTRIM(LTRIM(COALESCE(Last_Name, '') + COALESCE(' ' + First_Name, '') + COALESCE(' ' + Middle_Name, ''))) AS Name,
MGR_First_Name,
MGR_Last_Name
INTOEmployeeData
FROMEP_Data
N 56°04'39.16"
E 12°55'05.25"
April 24, 2008 at 8:26 am
Have a look at the 'Rules of Nulls' Article on here for an explanation
http://www.sqlservercentral.com/articles/Advanced+Querying/fourrulesfornulls/1915/
April 24, 2008 at 8:30 am
another option, however I think I read that coalese is the preferred way...Can anyone confirm that?
SELECT RTRIM(LTRIM(ISNULL(Last_Name, '') + ' ' + ISNULL(FIRST_Name, '') + ' ' + ISNULL(Middle_Name, ''))) as NAME
MGR_First_Name,
MGR_Last_Name
INTO EmployeeData
from ep_data
-- Cory
April 24, 2008 at 8:33 am
COALESCE is the ANSI compliant way, and offers multiple alternate values if the previous one is null.
ISNULL is arguably very slightly faster
April 24, 2008 at 8:36 am
steveb (4/24/2008)
COALESCE is the ANSI compliant way, and offers multiple alternate values is null.ISNULL is arguably very slightly faster
Thanks! I thought that was the case - that was how I remembered it...the "ANSI Compliant" way was the way that didnt make sense to me. 😛
-- Cory
April 24, 2008 at 9:40 am
works great.. thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply