Split string - 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

General SQL Server Forums

New to SQL Server Programming

Split string

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-06 : 10:44:03

in a client's table, there is a column for FirstName which, mistakenly, actually has the First Name, Middle Name and Suffix stuffed in there. There are seperate columns for MiddleName and Suffix which are all NULL right now. I would like to substring the firstname string out and put the everything after the first ' ' and before the second ' ' into MiddleName and anything after the second ' ' into Suffix.

How would this look in a substring?

thanks

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 11:17:05

If you post some sample data that would help

Is suffix Last name?

Suffix usually means

III
II
ESQ
JR
SR

ect

Are there any Prefixies?

Dr.
Drs.
Mr.
Mrs.
Ms.
Miss

???

What does this Give you?

SELECT LEN(FirstName) - LEN(REPLACE(FirstName,' ','')) AS [Spaces], COUNT(*) AS [Space_Rows]
FROM yourTable
GROUP BY LEN(FirstName) - LEN(REPLACE(FirstName,' ','')) AS [Spaces]

Post the results please

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-06 : 11:35:08

Suffix is as you purport, there are no prefixes, and your script blows up on the second "AS":
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'AS'.

Sample data would look like:

 
FIRSTNAME            MIDDLENAME                 LASTNAME                 SUFFIX
John Joseph Jr       NULL                       DOE                      NULL

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-06 : 11:51:14

Removing the second "AS" provides:


Spaces	Space_Rows
0	118644
1	117376
3	17
NULL	7
2	7091

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 12:11:22

my bad...copy and paste..oye

Good catch

Now, do you see the counts?

You look to have 118k+ rows that appear to be first name only
117k+ that looks to be first and last (or last and first..idk)
and 7k+ that are first middle last (or any combo)
3 special guys that are?
and 7 rows with nothing

Can you get a sample of each? See if this runs together, if not break it up..and post what you get from this

SELECT TOP 10 LEN(FirstName) - LEN(REPLACE(FirstName,' ','')) AS [Spaces], FirstName
FROM yourTable
WHERE LEN(FirstName) - LEN(REPLACE(FirstName,' ','')) = 0
UNION ALL
SELECT TOP 10 LEN(FirstName) - LEN(REPLACE(FirstName,' ','')) AS [Spaces], FirstName
FROM yourTable
WHERE LEN(FirstName) - LEN(REPLACE(FirstName,' ','')) = 1
UNION ALL
SELECT TOP 10 LEN(FirstName) - LEN(REPLACE(FirstName,' ','')) AS [Spaces], FirstName
FROM yourTable
WHERE LEN(FirstName) - LEN(REPLACE(FirstName,' ','')) = 2
UNION ALL
SELECT TOP 10 LEN(FirstName) - LEN(REPLACE(FirstName,' ','')) AS [Spaces], FirstName
FROM yourTable
WHERE LEN(FirstName) - LEN(REPLACE(FirstName,' ','')) = 3

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-06 : 12:19:22

Spaces FirstName
0 DONALD
0 LYNN
0 TINA
0 FRANKLIN

Spaces FirstName
1 BRYAN JOSEPH
1 MELVIN DOUGLAS
1 EDWARD LEE
1 BOBBY CLYDE
1 VICTOR SHERWIN

Spaces FirstName
2 JAMES A III
2 PARKS AVERY JR
2 JAMES EDWARD JR
2 KENNETH WESLEY JR

Spaces FirstName
3 A B JR MRS
3 ROBERT G SR MRS
3 JAMES M AKA WILLIAM
3 ANDREW D J III
3 ANDREW D J III

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 12:35:05

OK...so now what do you want to do?

Let me guess

Spaces = 0 Do nothing
Spaces = 1 I don't know which is first and which is middle
Spaces = 2 First middle suffix?
Spaces = 3 idk

You need to answer those questions

Then you will need to come up with a parsing routine

You can use CharIndex, but I'd also suggest a User Defined Function

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-06 : 12:37:57

And maybe you can use something like we did here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=172110

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-06 : 12:54:23

Spaces = 0 Do nothing - correct-a-mundo
Spaces = 1 first string is ALWAYS firstname and second can always go to Middlename
Spaces = 2 First middle suffix - correct-a-mundo
Spaces = 3 same rule as '2' but allow the last two segments into suffix.

I have the following to parse the leading characters out of an address string. it 'seems' i could modify this somehow to get the job done:

update mnione set housenuone =LEFT(addressline1,CASE WHEN CHARINDEX(' ',addressline1)> 0 THEN CHARINDEX(' ',addressline1) ELSE PATINDEX('%[A-Za-z]%',addressline1) END)
WHERE addressline1 LIKE '[0-9]%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources