Splitting a 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

SQL Server 2000 Forums

Transact-SQL (2000)

Splitting a String

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-12 : 18:14:35

declare @full_name varchar(100)
SET @full_name = 'John#David#Smith

How can split these three names to a separate variable.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-12 : 18:28:00

PARSENAME function works great for this, just as long as there aren't more than 4 parts to the string.

DECLARE @full_name varchar(100)

SET @full_name = 'John#David#Smith'
SET @full_name = REPLACE(@full_name, '#', '.')

SELECT PARSENAME(@full_name, 1) AS LastName
SELECT PARSENAME(@full_name, 2) AS MiddleName
SELECT PARSENAME(@full_name, 3) AS FirstName

Here is the SQLTeam.com article that discusses this:
http://www.sqlteam.com/item.asp?ItemID=15044

Tara Kizer
aka tduggan

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-12 : 19:03:19

Its works fine...Thanks a lot....Great

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-09-11 : 13:38:36

Here Iam facing another issue...
@full_name = 'John#David#Smith can come as different cases :
1. 'John#David or John#David#(In this case middle_name is null)
2. 'John#David#Smith# (In this case I should have first_name,middle_name and last_name)

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-09-11 : 14:12:28

I used this but still Iam not getting the required results
SELECT
first_name = CASE
WHEN PARSENAME(REPLACE(@full_name, '#', '.'), 3) IS NULL THEN PARSENAME(REPLACE(@full_name, '#', '.'), 2)
ELSE
PARSENAME(REPLACE(@full_name, '#', '.'), 3)
END,
middle_name = PARSENAME(REPLACE(@full_name, '#', '.'), 2),
last_name = PARSENAME(REPLACE(@full_name, '#', '.'), 1)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-11 : 14:50:45

parsing a string by a seperater is the easy part. It sounds like you need to determine all the rules for how to parse various names. Are the rules consistant? What are all the variation of how the following names could be coded with your seperater?

Thurston Howell III
Mr. T
Cher
Mr. and Mrs. Rodney Q. Dangerfield
Billy Bob Bubba Bodine

Be One with the Optimizer
TG

drdream
Starting Member

29 Posts

Posted - 2006-09-11 : 15:01:46

Would this help?

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

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-09-11 : 16:22:01

@full_name can appear in different ways

'John#David#Smith'
'John#David#Smith'
'John#David#'
'John#David'

I need to parse them into first name ,middle name and Last name

One strange case I found was It can be

'John#David#S.'
'John#D.#Smith'
'John.#David#Smith

Now I can't use the PARSENAME to parse it to first name,middle name and last name

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-09-11 : 16:24:03

@full_name can appear in different ways

'John#David#Smith'
'John#David#Smith#'
'John#David#'
'John#David'

I need to parse them into first name ,middle name and Last name

One strange case I found was It can be

'John#David#S.'
'John#D.#Smith'
'John.#David#Smith

Now I can't use the PARSENAME to parse it to first name,middle name and last name

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-11 : 17:28:04

Ok that logic is getting a little more than just splitting a csv. See if this helps you out:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56499

EDIT:
That function does a good job at seperating the name parts however since you have some cases where Middle and Last names are reversed depending on the length you will need to modify the final results with a case statement checking for LEN of those values.

perhaps something like this:



use pubs

--assumes the FormatName function is compiled in PUBS

set nocount on
declare @tb table 
	(rowid int identity(1,1)
	,full_name varchar(50)
	,firstname varchar(15)
	,middleName varchar(15)
	,lastname varchar(15))

insert @tb (full_name) 
select 'John#David#Smith' union all
select 'John#David#' union all
select 'John#David' union all
select 'John#David#S.' union all
select 'John#D.#Smith' 


select	full_name
	,F as firstName
	,case when len(L) = 1 then L else M end as middleName
	,case when len(L) = 1 then M else L end as LastName
from	(
	select full_name
		,dbo.FormatName(replace(full_name,'#', ' '), 'F') F
		,dbo.FormatName(replace(full_name,'#', ' '), 'M') M
		,dbo.FormatName(replace(full_name,'#', ' '), 'L') L
	from @tb
	) a


output:
full_name		firstName	MiddleName	LastName
-----------------       --------------  -------------   ----------
John#David#Smith	John		David		Smith
John#David#		John				David
John#David		John				David
John#David#S.		John		S		David
John#D.#Smith		John		D		Smith

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources