SQL statement to update a csv field with csv values

unikorn · #1 (**permalink**) 02-11-04, 18:08

I have a table with a field that has a comma seperated list of numbers, and the numbers stand for some other text. Is there a sql query I can write to replace the comma separated numbers with comma separated text.

What I need to do, is to convert

(select id, field1
from table_1)

TABLE 1

ID Field1
1 1,2,3
2 3,4
3 1,6
4 5
5 3,4

To

TABLE 2

ID Field1
1 Budweiser,Heineken,Corona
2 Corona,Busch
3 Budweiser,Amber
4 Guiness
5 Corona,Busch

------------------------------------
Using the following conversions.
1 -> Budweiser
2 -> Heineken
3 -> Corona
4 -> Busch
5 -> Guiness
6 -> Amber
-------------------------------------

Any help or suggestions would be appreciated.

If I need to I can manually enter every field since there are only 10 conversions to be made.

Thanks!

This is a suggestion i got but not sure how to apply the @string and @position etc.

=====================
BEGIN

SELECT @chval=ASCII(SUBSTRING(@string, @position,1)
@i = @i+1
if char(@chval) =','
begin
select @intval= substring(@string,@position, @i-1)
select type =
case
when @intval =1 then "Budweiser"
when @intval=2 then " "
.....
.....
end
@cnvtStr = @cnvtstr +','+@Type
@i=0
end

SET @position = @position + 1

END

-- update @cnvtstr

===================

llccoo · #2 (**permalink**) 02-12-04, 09:32

If the list of what the numbers should be is in another table then you can simply do the following query:

Code:

SELECT TBL1.ID AS ID,  TBL2.ID2 AS FIELD1
FROM TABLE1 TBL1, TABLE2 TBL2 , TABLE3 TBL3, TABLE4 TBL4
WHERE TBL1.FIELD1 = TABL2.NUM
AND TBL1.FIELD1 = TABL3.NUM (+)
AND TBL1.FIELD1 = TABL4.NUM (+)
GROUP BY ID;

hope this helps somewhat.

Quote:

Originally posted by unikorn
I have a table with a field that has a comma seperated list of numbers, and the numbers stand for some other text. Is there a sql query I can write to replace the comma separated numbers with comma separated text.

What I need to do, is to convert

(select id, field1
from table_1)

TABLE 1

ID Field1
1 1,2,3
2 3,4
3 1,6
4 5
5 3,4

To

TABLE 2

ID Field1
1 Budweiser,Heineken,Corona
2 Corona,Busch
3 Budweiser,Amber
4 Guiness
5 Corona,Busch

------------------------------------
Using the following conversions.
1 -> Budweiser
2 -> Heineken
3 -> Corona
4 -> Busch
5 -> Guiness
6 -> Amber
-------------------------------------

Any help or suggestions would be appreciated.

If I need to I can manually enter every field since there are only 10 conversions to be made.

Thanks!

This is a suggestion i got but not sure how to apply the @string and @position etc.

=====================
BEGIN

SELECT @chval=ASCII(SUBSTRING(@string, @position,1)
@i = @i+1
if char(@chval) =','
begin
select @intval= substring(@string,@position, @i-1)
select type =
case
when @intval =1 then "Budweiser"
when @intval=2 then " "
.....
.....
end
@cnvtStr = @cnvtstr +','+@Type
@i=0
end

SET @position = @position + 1

END

-- update @cnvtstr

===================

llccoo · #3 (**permalink**) 02-12-04, 09:35

just in case this is what the tables should look like:

Code:

TABLE1
ID Field1
1 1,2,3
2 3,4
3 1,6
4 5
5 3,4

TABLE2
NUM ID2
1 Budweiser
2 Heineken
3 Corona
4 Busch
5 Guiness
6 Amber

hopefully that makes it a little bit more clearer.

Quote:

Originally posted by llccoo
If the list of what the numbers should be is in another table then you can simply do the following query:

Code:

SELECT TBL1.ID AS ID,  TBL2.ID2 AS FIELD1
FROM TABLE1 TBL1, TABLE2 TBL2 , TABLE3 TBL3, TABLE4 TBL4
WHERE TBL1.FIELD1 = TABL2.NUM
AND TBL1.FIELD1 = TABL3.NUM (+)
AND TBL1.FIELD1 = TABL4.NUM (+)
GROUP BY ID;

hope this helps somewhat.

Internet Services

Web Development

Digital Marketing

Consumer Tech