how to split - 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 2005 Forums

Transact-SQL (2005)

how to split

Author

Topic

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-12-11 : 17:06:42

How to split

Mike J Anderson to 3 columns

FName MiddleName Lastname
Mike j Anderson

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-11 : 17:19:55

The PARSENAME function can do that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-11 : 18:52:40

select parsename(replace('Mike J Anderson',' ','.'),1)
select parsename(replace('Mike J Anderson',' ','.'),2)
select parsename(replace('Mike J Anderson',' ','.'),3)

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-12 : 01:12:51

are you sure you'll have three part names always?

Deepak1983
Starting Member

23 Posts

Posted - 2009-12-12 : 08:40:47

Thanks to webfred

The below query will divide single column into three columns

SELECT PARSENAME(REPLACE('Mike J Anderson',' ','.'),3)AS FirstName,
PARSENAME(REPLACE('Mike J Anderson',' ','.'),2) AS MiddleName,
PARSENAME(REPLACE('Mike J Anderson',' ','.'),1) AS LastaName

Deepak Arora

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-12-12 : 13:30:08

No, It can be FNAme and LastName or Fname,midname,lastname.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-12 : 14:12:04

It should also work if there are only two parts.
Give it a try...

No, you're never too old to Yak'n'Roll if you're too young to die.

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-12-12 : 15:27:38

quote:
Originally posted by webfred

It should also work if there are only two parts.
Give it a try...

No, you're never too old to Yak'n'Roll if you're too young to die.

I have name in this format

1)MiKE LOEWY-FELL
2)MIKE J ANDERSON
3)JOHN ADAMS

How do I handle above three types so that it splits.
3)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-12 : 17:12:44

It is (not) funny how much your last example differs from your first example.
What's coming up next?

No, you're never too old to Yak'n'Roll if you're too young to die.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-12 : 17:35:31

SCHEMA, have you even tried using the PARSENAME function. It does what you want. Look at the examples provided. When you have only two names, then you can use the CASE statement along with PARSENAME.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-12-13 : 16:53:39

I tried but What to put in Case

Like
Case When COL like ????

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-14 : 01:20:10

Split to any number of columns
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/11/splitting-delimited-data-to-columns-set-based-approach.aspx

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-12-14 : 12:28:41

Here is a function that will parse western-style names for you:
http://sqlblindman.pastebin.com/f68f37c15

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources