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
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: