Substring function for name

Question

Substring function for name

timscronin

SSCertifiable

Points: 6851
More actions
October 21, 2008 at 9:58 am

#194923

I 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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

In your formula for firstname, you have PATINDEX looking for '%, %', but then taking the 1st LEFT letter, so it will always be returning the space after the comma.

In your formula for middlename, you have PATINDEX looking for '% %' which will find the first space, which is after the last name. CHARINDEX may be easier to use here, because you can specify a start location

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

here's my attempt, after pondering this over lunch:

SELECT id, LEFT(iname, CommaLoc-1) AS lastname,

SUBSTRING(iname, CommaLoc + 2, Space2Loc - CommaLoc - 1) AS firstname,

SUBSTRING(iname, Space2Loc + 1, NameLen - Space2Loc) AS middlename

FROM (SELECT id, iname,

LEN(iname) AS NameLen,

CHARINDEX(',', iname) AS CommaLoc,

CHARINDEX(' ', iname, CHARINDEX(',', iname) + 2) AS Space2Loc

FROM #bogus2) sq

[\code]

Gary Johnson-259336 SSCommitted Points: 1673 More actions · Answer 3

This is actually a tough question as you never know how the names are formatted or if there are two names for the last name such as "Van Allen".

Here is a partial query for getting just the last name. It doesn't solve the issue of a name like 'Jerome Jack Van Allen' or 'Jerome Van Allen' so I'll leave it up to you to figure that out. 🙂

IF OBJECT_ID('tempdb..#Foo') IS NOT NULL

DROP TABLE #Foo

CREATE TABLE #Foo

(

ID int,

FullName varchar(255)

)

INSERT INTO #Foo(ID, FullName)

SELECT 123, 'Watkins, Glen Mark'

UNION SELECT 133,'Smith, Jerome Alan'

UNION SELECT 134,'Van Allan, Jerome'

UNION SELECT 135,'Jerome Van Allan'

UNION SELECT 136,'Jerome Jack Van Allan'

UNION SELECT 137,'Van Allan, Jerome Jack'

SELECT id,

FullName,

LastName = CASE PATINDEX('%,%', FullName)

WHEN 0 -- We dont have a comma

THEN CASE PATINDEX('% %', FullName)

WHEN 0 THEN FullName -- No comma or space so return everything

ELSE -- no comma but have a space so get everything after the first space

SUBSTRING( FullName, PATINDEX('%,%', FullName) + 1, LEN(FullName))

END

ELSE SUBSTRING(FullName, 1, PATINDEX('%,%', FullName) - 1)

END

FROM #Foo

Gary Johnson
Sr Database Engineer

krayknot SSCertifiable Points: 7998 More actions · Answer 4

timscronin (10/21/2008)
I 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

Please let me know the standard name format

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

ck9663 Right there with Babe Points: 763 More actions · Answer 5

Try the following code. Just make sure there's a single comma that separate last name from fist and middle names

set nocount on

declare @tblName table (id int, names varchar(50))

insert into @tblName values (123, 'Watkins, Glen Mark')

insert into @tblName values (133, 'Smith, Jerome Alan')

insert into @tblName values (133, 'Kent, Clark')

insert into @tblName values (133, 'Van Allen, Don Martin')

select

SUBSTRING(names,1, CHARINDEX(',',names,0)-1) as lastname,

rtrim(SUBSTRING(ltrim(substring(names, CHARINDEX(',',names,0)+ 1, len(names))), 1, case when CHARINDEX(' ', ltrim(substring(names, CHARINDEX(',',names,0)+ 1, len(names))),0) > 0 then CHARINDEX(' ', ltrim(substring(names, CHARINDEX(',',names,0)+ 1, len(names))),0) else LEN(names) end)) as firstname,

middlename =

case when CHARINDEX(' ', ltrim(substring(names, CHARINDEX(',',names,0)+ 1, len(names))),0) > 0 then

ltrim(rtrim(substring(names, len(SUBSTRING(names,1, CHARINDEX(',',names,0)) + SUBSTRING(ltrim(substring(names, CHARINDEX(',',names,0)+3, len(names))), 1, case when CHARINDEX(' ', ltrim(substring(names, CHARINDEX(',',names,0)+ 1, len(names))),0) > 0 then CHARINDEX(' ', ltrim(substring(names, CHARINDEX(',',names,0)+ 1, len(names))),0) else LEN(names) end)) + 1, len(names))))

else

''

end

from @tblname

Happy Coding!

-- CK