How to merge three columns to one column?
-
In my table, I have three columns: first_name, last_name, middle_name . I tried to merge them into one column, and this is what I did:
select first_name + ' ' + last_name + ' ' + middle_name as Full_Name from contactsHowever, when I do a query on that one, only the ones that have data in all three columns show and if one of the column is missing data wouldn't show. for example, orginal table -------------------------------------- |first_name | last_name | Middle_name| -------------------------------------- test | test | null | -------------------------------------- when merges: ------------------------- | Full_Name | ------------------------- | null | ------------------------- if column middle_name is empty, then Full_name would return null, eventhough first_name and last_name have data, why is that happening? Could someone help me how to make that work? Thanks a bunch. John -
In my table, I have three columns: first_name, last_name, middle_name . I tried to merge them into one column, and this is what I did:
select first_name + ' ' + last_name + ' ' + middle_name as Full_Name from contactsHowever, when I do a query on that one, only the ones that have data in all three columns show and if one of the column is missing data wouldn't show. for example, orginal table -------------------------------------- |first_name | last_name | Middle_name| -------------------------------------- test | test | null | -------------------------------------- when merges: ------------------------- | Full_Name | ------------------------- | null | ------------------------- if column middle_name is empty, then Full_name would return null, eventhough first_name and last_name have data, why is that happening? Could someone help me how to make that work? Thanks a bunch. Johnviettho wrote: why is that happening? Because interaction with null produces null. e.g. 1 + null = null viettho wrote: Could someone help me how to make that work? Yes.
SELECT CASE first_name WHEN NULL THEN '' ELSE first_name + ' ' END +
CASE last_name WHEN NULL THEN '' ELSE last_name + ' ' END+
CASE middle_name WHEN NULL THEN '' ELSE middle_name END AS Full_Name
FROM contactsDoes this help?
Do you want to know more? WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
Upcoming talk: SELECT UserName, Password FROM Users -- Getting unauthorised access to a SQL Server, and how to prevent it.
-
viettho wrote: why is that happening? Because interaction with null produces null. e.g. 1 + null = null viettho wrote: Could someone help me how to make that work? Yes.
SELECT CASE first_name WHEN NULL THEN '' ELSE first_name + ' ' END +
CASE last_name WHEN NULL THEN '' ELSE last_name + ' ' END+
CASE middle_name WHEN NULL THEN '' ELSE middle_name END AS Full_Name
FROM contactsDoes this help?
Do you want to know more? WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
Upcoming talk: SELECT UserName, Password FROM Users -- Getting unauthorised access to a SQL Server, and how to prevent it.