T-SQL: Parse single name field into first name and last name

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

Advertisements

2017

04/22

CATEGORY

Tutorials

Write comment