how to split name into first and last name in SQL

Question

I have a table that contain first name and last name as below

First_Name LAST_NAME

John       Milano
Garry      Sanders 
           Barr, Jerome P
           Venti, Louis 

I need to correct some rows which has complete name in the column Last_name. My desired output is:-

First_Name LAST_NAME

John       Milano
Garry      Sanders 
Jerome     Barr
Louis      Venti

Thanks in advance!

Answer 1

This will get you close, with a couple caveats: 1) Some first names have two words -- "Mary Ann", etc. So, you probably want to keep those. 2) You only want to change rows where the LAST_NAME column contains a comma.

UPDATE MyTable
    SET First_Name = LTRIM(SUBSTRING(LAST_NAME, CHARINDEX(',', LAST_NAME) + 1, 100)),
        LAST_NAME = LTRIM(RTRIM(SUBSTRING(@name, 0, CHARINDEX(',', @name))))
WHERE CHARINDEX(',', LAST_NAME) > 0

So, you will have "Jerome P" as the first name still. If you want to restrict first names to a single word, you would have to do a bit more. This should work:

UPDATE MyTable
    SET First_Name = LTRIM(RTRIM(SUBSTRING(@name, 0, CHARINDEX(' ', @name))))
WHERE CHARINDEX(' ', First_Name) > 0