Shahsra Shahsra - 1 month ago 4
SQL Question

How to split a single column values to multiple column values?

I have a problem splitting single column values to multiple column values.

For Example:

Name
------------
abcd efgh
ijk lmn opq
asd j. asdjja
asb (asdfas) asd
asd


and I need the output something like this:

first_name last_name
----------------------------------
abcd efgh
ijk opq
asd asdjja
asb asd
asd null


The middle name can be omitted (no need for a middle name) The columns are already created and need to insert the data from that single
Name
column.

Answer

Your approach won't deal with lot of names correctly but...

SELECT CASE
         WHEN name LIKE '% %' THEN LEFT(name, Charindex(' ', name) - 1)
         ELSE name
       END,
       CASE
         WHEN name LIKE '% %' THEN RIGHT(name, Charindex(' ', Reverse(name)) - 1)
       END
FROM   YourTable