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

Question

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 1

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