split one field into three fields in same table

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 one field into three fields in same table

Author

Topic

smartlizard
Starting Member

24 Posts

Posted - 2001-10-28 : 12:31:30

I have a field in my table called "name" it has the following info contained in it...

"RONALD * EDWARD * SELL"

I want to create three addtional fields in the table...

FName
MName
LName

Then I want to take the correspoonding info from the "Name" field and put it into it's respected FName, MName or LName.

I need to do this on a table with 2.9 Million redords.

Can you show me what to do? What would be the SQL statement?

Thanks! HAVE A GREAT WEEK!

Ron Sell
http://smartLIZARD.com

guldmann
Starting Member

1 Post

Posted - 2006-11-21 : 12:36:07

SET NOCOUNT ON

DECLARE @Table table (Col001 varchar(100) NOT NULL)

INSERT INTO @Table VALUES('AABY TAMARA J')
INSERT INTO @Table VALUES('AADNESEN MARJORIE')
INSERT INTO @Table VALUES('Paola Godoy Calcagno Jensen')
INSERT INTO @Table VALUES('AARANT MAUREEN R')
INSERT INTO @Table VALUES('AARON MICHELLEE')
INSERT INTO @Table VALUES('AARON PATRICIA J')
INSERT INTO @Table VALUES('ABBA DAVID W')
INSERT INTO @Table VALUES('ABBATE ANTHONY P')

SELECT LEFT(Col001,CHARINDEX(' ',Col001)) AS FName
,CASE
WHEN LEN(Col001)-(CHARINDEX(' ',Col001)+CHARINDEX(' ',REVERSE(Col001))) > 0 THEN
SUBSTRING(Col001,CHARINDEX(' ',Col001)+1,LEN(Col001)-(CHARINDEX(' ',Col001)+CHARINDEX(' ',REVERSE(Col001))))
ELSE
''
END AS MName
,RIGHT(Col001,CHARINDEX(' ',REVERSE(Col001))-1) AS LName
FROM @Table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources