Use this handy function to parse a single name field into two separate first name and last name fields. Accepts name formats like:
Borges, Jorge L.
Borges, Jorge
Jorge Luis Borges
Jorge Borges
Use it like:
INSERT INTO new_authors SELECT ParseName(name,'first'), ParseName(name,'last') FROM authors
Function definition:
create FUNCTION [dbo].[ParseName] ( -- Add the parameters for the function here @Name varchar(250), @NameType varchar(5) ) RETURNS varchar(250) AS BEGIN Declare @return_name varchar(250) select @return_name = case when charindex(',', @Name) > 0 then case when @NameType = 'last' then substring(@Name,0,charindex(',', @Name)) else LTRIM(substring(@Name,charindex(',', @Name) + 1,LEN(@Name))) end else case when @NameType = 'last' then RIGHT(@Name, CHARINDEX(' ', REVERSE(' ' + @Name)) - 1) else case when LEN(@Name) - LEN(REPLACE(@Name, ' ', '')) = 2 then LEFT(@Name, CHARINDEX(' ', REVERSE(' ' + @Name)) - 1) else substring(@Name,0,charindex(' ', @Name)) end end end -- Return the result of the function RETURN @return_name END