Split Names With out VB Script

Question

Post reply

Split Names With out VB Script

sridevi.chiru

Newbie

Points: 6
More actions
December 25, 2008 at 11:45 pm

#199397

hi ,
In the source Field PATNAME data look likes
John,David Robert
sustaita,corie m
pena,geneva jesus
i want output splitted in to 3 fields(with out vb script )like
Field1 Field2 Field3
john David Robert
sustaita Corie m
pena geneva jesus

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

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

ps. One Orange Chip Points: 29423 More actions · Answer 1

create table #patname

(

FullName varchar(100),

FirstName varchar(100),

MiddleName varchar(100),

LastName varchar(100)

)

insert into #patname(FullName)

select 'John,David Robert'

UNION ALL

select 'sustaita,corie m'

UNION ALL

select 'pena,geneva jesus'

-- Assumption, the field full name contains last name, a comma, first name, a space and middle name

update #patname

set lastName=left(fullname, charindex(',', fullname)-1),

firstName=substring(fullname, charindex(',', fullname)+1,charindex(' ', fullname)-charindex(',', fullname)-1),

middleName=right(fullName, len(fullName)-charindex(' ', fullname))

from #patname

select * from #patname

output

----------

FullName FirstName MiddleName LastName

John,David RobertDavid Robert John

sustaita,corie mcorie m sustaita

pena,geneva jesusgeneva jesus pena

Pradeep Singh

Dugi SSCoach Points: 17998 More actions · Answer 2

Yea, nice solution from ps!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

Using the test data nicely provided by Pradeep, with an added row...

create table #patname

(

FullName varchar(100),

FirstName varchar(100),

MiddleName varchar(100),

LastName varchar(100)

)

insert into #patname(FullName)

select 'John,David Robert'

UNION ALL

select 'sustaita,corie m'

UNION ALL

select 'pena,geneva jesus'

UNION ALL

SELECT 'Moden,Jeff'

... we can make it so that a middle name is not required. Still, it won't handle names like "St. James, Timothy R" as it is, but it gets you closer...

SELECT FullName,

CASE WHEN PARSENAME(FullName,3) IS NULL

THEN PARSENAME(FullName,1)

ELSE PARSENAME(FullName,2)

END AS First,

CASE WHEN PARSENAME(FullName,3) IS NULL

THEN NULL

ELSE PARSENAME(FullName,1)

END AS Middle,

CASE WHEN PARSENAME(FullName,3) IS NULL

THEN PARSENAME(FullName,2)

ELSE PARSENAME(FullName,3)

END AS Last

FROM (

SELECT REPLACE(REPLACE(FullName,',','.'),' ','.') AS FullName

FROM #patname

)d

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ps. One Orange Chip Points: 29423 More actions · Answer 4

Jeff, I must say the way you play around with T-SQL is really amazing. Atleast I couldnt think of using PARSENAME in this example:w00t:

Pradeep Singh

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

ps (12/26/2008)
Jeff, I must say the way you play around with T-SQL is really amazing. Atleast I couldnt think of using PARSENAME in this example:w00t:

:blush: That's awfully nice of you to say... Thanks Pradeep.

The real fact is, the use of CHARINDEX, like you did, is probably a lot faster because I had to use a double nested REPLACE to get mine to work. Just wanted to show a different way.

I'm thinking that RegEx would be much better at splitting names...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)