Parsing First and Last Names - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Parsing First and Last Names

Author

Topic

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-08-26 : 16:12:01

Hello all...

I'm hoping you could point me in the right direction with the following problem I'm having.

I have a column populated with the follwing rows:

Susan Garcia
LeVan Manufacturing
Lou Ann Balisi
Michelle Gilliland

I need to find a way to break apart the FirstNames, MiddleInitials, and LastNames into their own columns.

I remember vaguely reading something on how to do this in Microsoft's site but I can't seem to find it.

Please help.

thanks.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-08-26 : 18:24:20

Well, I don't have a full script for ya, but I can point you in the right direction methinks.

Look up PATINDEX in BOL.
That should allow you to find the space in between the names.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-08-26 : 18:34:48

This is a bit closer....
It needs more work though


CREATE TABLE #Names(FullName VARCHAR(100), FirstName VARCHAR(50), LastName VARCHAR(50))
DECLARE @FullName VARCHAR(100)
DECLARE @separator_position1 INT
DECLARE @separator_position2 INT
DECLARE @separator_position3 INT


INSERT INTO #Names(FullName, FirstName, LastName) VALUES('John Smith', '', '')
INSERT INTO #Names(FullName, FirstName, LastName) VALUES('John Doe', '', '')
INSERT INTO #Names(FullName, FirstName, LastName) VALUES('Michael S Smith', '', '')
INSERT INTO #Names(FullName, FirstName, LastName) VALUES('Michael S Smith JR', '', '')

SELECT FullName, PATINDEX('% %', FullName) as FirstSpace, PATINDEX('% %', Right(FullName, PATINDEX('% %', FullName))) as SecondSpace,  PATINDEX('% %', RIGHT(FullName, PATINDEX('% %', Right(FullName, PATINDEX('% %', FullName))))) as ThirdSpace FROM #Names

DROP TABLE #Names

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources