split name into firstname, lastname and middle nam

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)

split name into firstname, lastname and middle nam

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2012-06-13 : 10:26:16

Hello,

Please help me to write sql statement that I can split the field name into 3 different column: FISTNAME, LASTNAME, MIDDLENAME

For example,
Column "NAME" displayed as below

IGORIA MYKYTIUKANKUN
JEANNNY C CLOUSE
MIMMRAY KRAUSS &
LONGER SR B LONGER C
MARKET P JENHOPES CO
MICHALE R BROWN
RICK J MANHEM

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-13 : 10:52:57

whats will be split up in case of MIMMRAY KRAUSS &?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-13 : 10:56:12

for others this would suffice based on sample data posted


SELECT NAME,
LEFT(NAME,CHARINDEX(' ',NAME + ' ')-1) AS FirstName,
CASE WHEN LEN(NAME) - LEN(REPLACE(NAME,' ','')) > 1  THEN PARSENAME(REPLACE(NAME,' ','.'),2) ELSE NULL END AS MiddleName,
CASE WHEN LEN(NAME) - LEN(REPLACE(NAME,' ','')) > 0 THEN PARSENAME(REPLACE(NAME,' ','.'),1) ELSE NULL END AS LastName
FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ntn104
Posting Yak Master

175 Posts

Posted - 2012-06-13 : 13:23:17

With this case, I think I will replace "&" for ''. Thanks,

quote:
Originally posted by visakh16

whats will be split up in case of MIMMRAY KRAUSS &?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ntn104
Posting Yak Master

175 Posts

Posted - 2012-06-13 : 13:27:41

An error message for "parsename" not found. What function should we replace for that....I guess my sql not support that function.

Thanks,

quote:
Originally posted by visakh16

for others this would suffice based on sample data posted

SELECT NAME,
LEFT(NAME,CHARINDEX(' ',NAME + ' ')-1) AS FirstName,
CASE WHEN LEN(NAME) - LEN(REPLACE(NAME,' ','')) > 1 THEN PARSENAME(REPLACE(NAME,' ','.'),2) ELSE NULL END AS MiddleName,
CASE WHEN LEN(NAME) - LEN(REPLACE(NAME,' ','')) > 0 THEN PARSENAME(REPLACE(NAME,' ','.'),1) ELSE NULL END AS LastName
FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-13 : 15:32:51

are you not using MS SQL Server? if not, you may be better off posting this in relevant forum. This is MS SQL SErver forum and solutions given are sql server specific mostly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ntn104
Posting Yak Master

175 Posts

Posted - 2012-06-13 : 16:03:48

I do use MS SQL sever 6.5

quote:
Originally posted by visakh16

are you not using MS SQL Server? if not, you may be better off posting this in relevant forum. This is MS SQL SErver forum and solutions given are sql server specific mostly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-13 : 16:32:26

oh...didnt realise you're still using such an old version. you should have posted it in correct forum then!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources