Simplify my parsing problem - 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

SQL Server Development (2000)

Simplify my parsing problem

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-07-29 : 16:24:22

I've got a username that can be any of the forms:

john.doe
john.r.doe
john.r.doe.jr

I need to parse each of the names into 3 variables:
@Firstname
@Middlename
@Lastname

I don't need the "jr".

Is there an easy way to do this with patindex?

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-29 : 16:39:45

Come on sam...

You already forget robs parsename article?

You can write your own though....

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2004-07-29 : 17:15:37

Hmmm. This trickier than I thought.

Try figuring out that

joe.r.doe

and

joe.doe.jr

Argh.

I'll look at Rob's article.

Thanks for reminding me.

Sam

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-07-29 : 17:47:49

Sam,
If you look at the last item in the array of name parts, you can filter out all suffix's like Sr Jr III IV Esq etc.

Michael

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

SamC
White Water Yakist

3467 Posts

Posted - 2004-07-29 : 18:20:46

What ? Array of name parts ? Something I should build or part of Rob's paper?

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-07-29 : 18:25:07

I didn't read Rob's paper, but I was assuming that you had split a name up into it's parts into some sort of array.
If the last element of that array is "Jr" or "Sr" etc etc then the last name is the previous element.

Michael

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

SamC
White Water Yakist

3467 Posts

Posted - 2004-07-29 : 18:30:31

OK and thanks for claifying that.

I'm doing a different check, same kind of thing. If the Middle Initial is longer than 2 characters, I assume it's the last name and no middle initial is present.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-30 : 11:33:29

[code]
USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(50))
GO

INSERT INTO myTable99(Col1)
SELECT 'john.doe' UNION ALL
SELECT 'john.r.doe' UNION ALL
SELECT 'john.r.doe.jr'
GO

SELECT parsename(Col1,4)
, parsename(Col1,3)
, parsename(Col1,2)
, parsename(Col1,1)
FROM myTable99
GO

DROP TABLE myTable99
GO

[/code]

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources