comma seperated value - 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

General SQL Server Forums

New to SQL Server Programming

comma seperated value

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-07-16 : 13:44:47

Hi all,

i have salespersons table in which three colums are available for salespersons and right now salesperson1 column has been populated with
the comma seperated salespersons name like this
saleperson1 salesperson2 salesperson3
vijay,john,tom,jerry
wilson,marry
peter,rob,harry,boby

salesperson2 and salesperson3 are not yet populated .i want to take out the only first three salespersons name and should be populated into into salespaeron1 ,salesperson2 ,saleperson3.

The new upadated table should be like this

salesperon1 salesperson2 salesperson3
vijay john tom
wilson marry null
peter rob harry

I hope my question is clear to you all.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-16 : 14:37:06

Here is one way:

DECLARE @Table TABLE (saleperson1 VARCHAR(100), salesperson2 VARCHAR(100), salesperson3 VARCHAR(100))
INSERT @Table (saleperson1)
SELECT 'vijay,john,tom,jerry'
UNION ALL SELECT 'wilson,marry'
UNION ALL SELECT 'peter,rob,harry,boby'

UPDATE @Table
SET
	saleperson1 = REVERSE(PARSENAME(REVERSE(REPLACE(saleperson1, ',', '.')), 1)),
	salesperson2 = REVERSE(PARSENAME(REVERSE(REPLACE(saleperson1, ',', '.')), 2)),
	salesperson3 = REVERSE(PARSENAME(REVERSE(REPLACE(saleperson1, ',', '.')), 3))


SELECT *
FROM @Table

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-07-16 : 14:59:49

Thanks for your help your solution is working fine when your declare a table variable but if i imply your solution in following manner it doesnt give proper solution my stepas are

create table salespersons( sales1 varchar(50),sales2 varchar(50),sales3 varchar(50))

INSERT salespersons (sales1)
SELECT 'vijay,john,tom,jerry'
UNION ALL SELECT 'wilson,marry'
UNION ALL SELECT 'peter,rob,harry,boby'

select * from salespersons

UPDATE salespersons
SET
sales1 = REVERSE(PARSENAME(REVERSE(REPLACE(sales1, ',', '.')), 1)),
sales2 = REVERSE(PARSENAME(REVERSE(REPLACE(sales2, ',', '.')), 2)),
sales3 = REVERSE(PARSENAME(REVERSE(REPLACE(sales3, ',', '.')), 3))

it gives me following output
sales1 sales2 sales2
vijay NULL NULL
wilson NULL NULL
peter NULL NULL

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-07-16 : 15:25:49

Just another approach:


UPDATE salespersons
   SET sales1 = CASE LEN(sales1) - LEN(REPLACE(sales1, ',', ''))
                     WHEN 0 THEN sales1
                     ELSE SUBSTRING(sales1, 1, CHARINDEX(',', sales1) - 1)
                END,
       sales2 = CASE LEN(sales1) - LEN(REPLACE(sales1, ',', ''))
                     WHEN 0 THEN NULL
                     WHEN 1 THEN SUBSTRING(sales1,
                                    CHARINDEX(',', sales1 + ',') + 1,
                                    LEN(sales1) - CHARINDEX(',', sales1))
                     ELSE SUBSTRING(sales1,
                             CHARINDEX(',', sales1) + 1,
                             CHARINDEX(',', sales1, CHARINDEX(',', sales1) + 1) - CHARINDEX(',', sales1) - 1)
                END,
       sales3 = CASE LEN(sales1) - LEN(REPLACE(sales1, ',', ''))
                     WHEN 0 THEN NULL
                     WHEN 1 THEN NULL
                     WHEN 2 THEN SUBSTRING(sales1,
                                    CHARINDEX(',', sales1, CHARINDEX(',', sales1) + 1) + 1,
                                    LEN(sales1) - CHARINDEX(',', sales1, CHARINDEX(',', sales1) + 1))
                     ELSE SUBSTRING(sales1,
                             CHARINDEX(',', sales1, CHARINDEX(',', sales1) + 1) + 1,
                             CHARINDEX(',', sales1,
                                CHARINDEX(',', sales1,
                                   CHARINDEX(',', sales1) + 1) + 1) - CHARINDEX(',', sales1, CHARINDEX(',', sales1) + 1) - 1)
                END;

quote:
if i imply your solution in following manner it doesnt give proper solution my stepas are

create table salespersons( sales1 varchar(50),sales2 varchar(50),sales3 varchar(50))

INSERT salespersons (sales1)
SELECT 'vijay,john,tom,jerry'
UNION ALL SELECT 'wilson,marry'
UNION ALL SELECT 'peter,rob,harry,boby'

select * from salespersons

UPDATE salespersons
SET
sales1 = REVERSE(PARSENAME(REVERSE(REPLACE(sales1, ',', '.')), 1)),
sales2 = REVERSE(PARSENAME(REVERSE(REPLACE(sales2, ',', '.')), 2)),
sales3 = REVERSE(PARSENAME(REVERSE(REPLACE(sales3, ',', '.')), 3))

By the way the original solution of Lamprey is right, and you should use sales1 instead of sales2 and sales3 in your query as the original solution.

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-07-16 : 23:32:29

what silly mistake i did ......thanks for addressing it ....all thanks
for all your help..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources