Substring function for name

Last Post 21 Oct 2008 08:59 AM by nosepicker. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
timcronin
New Member
New Member

--
21 Oct 2008 08:34 AM
have an imported record set where the name is just one field in a table. I need to split it out to last,first,middle.

Here is what data looks like

ID NAME
123 Watkins, Glen Mark
133 Smith, Jerome Alan

I tried this but not there yet, what am I missing

select id, substring (iname, 1, patindex( '%,%' , iname) -1 ) 'lastname'
,left(substring (iname, patindex( '%, %', iname) + 1, len(iname)),1)'firstname',
substring (iname, patindex( '% %', iname) + 1, len(iname))'middlename'
FROM table
nosepicker
New Member
New Member

--
21 Oct 2008 08:59 AM
Here are couple of ways to do this. First is the brute force direct method:

DECLARE @name varchar(50)
SET @name = 'Watkins, Glen Mark'

SELECT
LEFT(@name, CHARINDEX(',', @name) -1) AS LastName,
SUBSTRING(@name, CHARINDEX(', ', @name) +2, CHARINDEX(' ', @name, CHARINDEX(', ', @name) +3) - CHARINDEX(', ', @name) -2) AS FirstName,
RIGHT(@name, CHARINDEX(' ', REVERSE(@name)) -1) AS MiddleName


Second is using a built-in SQL Server function called "parsename". It's meant to separate 4-part object names like "Server.Database.Owner.Table", but it can be used for anything else that is separated by a maximum of 3 periods:

DECLARE @name varchar(50)
SET @name = 'Watkins, Glen Mark'

SELECT
PARSENAME(REPLACE(REPLACE(@name, ',', ''), ' ', '.'), 2) AS FirstName,
PARSENAME(REPLACE(REPLACE(@name, ',', ''), ' ', '.'), 1) AS MiddleName,
PARSENAME(REPLACE(REPLACE(@name, ',', ''), ' ', '.'), 3) AS LastName
You are not authorized to post a reply.

Acceptable Use Policy