split a field into multiple colomns

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)

split a field into multiple colomns

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-08-13 : 17:53:58

hi

i have a colomn called name
the data in it is stored in the following manner

lastname,firsname i.e doe,john
smith,john

i wnat split the field into two separate fileds such as lastname and firstname.

How can i achieve this..thanks in advance

Humate
Posting Yak Master

101 Posts

Posted - 2010-08-13 : 18:20:18

Is it always seperated by comma? Maybe you can work with this:

DECLARE	@Names TABLE
	(
		Name VARCHAR (50),
		Surname VARCHAR (50),
		Firstname VARCHAR (50)
	)

INSERT @Names

SELECT 'doe,john', NULL, NULL UNION ALL
SELECT 'cratchet,bob', NULL , NULL UNION ALL
SELECT 'jordan,michael', NULL, NULL

UPDATE @Names

SET 
Surname = LEFT(LTRIM(RTRIM(name)),CHARINDEX(',',LTRIM(RTRIM(name)))-1),
Firstname = RIGHT(LTRIM(RTRIM(name)),LEN(LTRIM(RTRIM(name)))-CHARINDEX(',',LTRIM(RTRIM(name))))

SELECT * FROM @Names

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 01:05:10

assuming format is consistent ie. there will be two parts of name separated by , you can use



UPDATE @Names

SET 
Surname = PARSENAME(REPLACE(name,',','.'),1),
Firstname = PARSENAME(REPLACE(name,',','.'),2)

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

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-08-16 : 11:22:21

thanks visakh and humate . Actually

there are some names with the middle intial in the following way:

doe,john m
smith,james F

How can split the middle intial now...thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-16 : 11:25:30

[code]UPDATE @Names

SET
Surname = PARSENAME(REPLACE(REPLACE(name,',','.'),' ','.'),3),
MiddleInitial = PARSENAME(REPLACE(REPLACE(name,',','.'),' ','.'),1),
Firstname = PARSENAME(REPLACE(name,',','.'),2)
[/code]

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

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-08-16 : 13:48:57

thank you visakh it works but

when there is no middle intial for a name then it returns null value

like

smith,john

its returns the value only for smith,john R

ANd there is one more problem...

there is trailing space in front of the first name like
smith,(space) john R

thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-16 : 13:53:07

what other formats can name come?

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

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-08-16 : 14:16:35

thats the only format

ex:

doe,john
smith, james R
ann, mary K

so..on

this is the standard format with which the field is populated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-16 : 14:29:19

[code]UPDATE @Names

SET
Surname = LEFT(name,CHARINDEX(',',name)-1),
MiddleInitial = CASE WHEN CHARINDEX(' ',name,CHARINDEX(',',name))>0 THEN SUBSTRING(name,CHARINDEX(' ',name,CHARINDEX(',',name))+1,1) ELSE NULL END,
Firstname = LTRIM(SUBSTRING(name,CHARINDEX(',',name)+1,CASE WHEN CHARINDEX(' ',name,CHARINDEX(',',name))>0 THEN LEN(name)-2 ELSE LEN(name) END))
[/code]

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

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-08-16 : 15:05:26

thanks visakh for your constant help .....

actually after much research i found out there is another field
customer name where the names are in the following format

john Doe
John R smith
James A mary

So i used this query below

DECLARE @FullName VARCHAR(100)
SET @FullName = 'John Doe'

SELECT SUBSTRING(@FullName, 1, CHARINDEX(' ', @FullName) - 1) AS [FirstName],
SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1, LEN(@FullName)) AS [LastName]

the output i got is like this:

firstname Lastname
john DOE
John R Smith

etc

can you help me with separating the middle intial from the lastname

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-17 : 10:57:50

[code]UPDATE @Names

SET
Surname = PARSENAME(REPLACE(REPLACE(name,',','.'),' ','.'),1),
MiddleInitial = PARSENAME(REPLACE(REPLACE(name,',','.'),' ','.'),2),
Firstname = PARSENAME(REPLACE(name,',','.'),1)
[/code]

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

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-08-17 : 16:37:31

thanks visakh 16 ..it worked

btw you blog is very nice..hope you keep it updated regurlarly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources