Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    1

    Arrow Unanswered: SQL statement to update a csv field with csv values

    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

    ===================
    Last edited by unikorn; 02-11-04 at 19:12.

  2. #2
    Join Date
    Nov 2003
    Posts
    65

    Exclamation Re: SQL statement to update a csv field with csv values

    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.

    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

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

  3. #3
    Join Date
    Nov 2003
    Posts
    65

    Re: SQL statement to update a csv field with csv values

    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.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •