Search code examples
sqlsql-servert-sqlstring

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.

Solution

  • 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