Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2007
    Posts
    1

    Question Unanswered: processing comma-delimited strings (redux)

    Hello,

    I have no trouble converting a comma-delimited string of values into multiple records, bur have recently encountered a problem that's giving me a headache - hopefully someone on the forum already has some experience doing this task:

    create table tester (col1 int, col2 varchar(1000), col2 varchar(1000))
    insert into tester values(1, '1,3,5,7', 'a,c,e,g,')
    insert into tester values(2, '11,13,15,17', 'aa,ac,ae,ag,')

    There is no correlation between rows, but between the 2 varchar columns is a positional relationship - in the first record, the '1' in col2 relates to the 'a' in col 3, same for the '3' and 'c', on and on. The values within each of the comma-delimited strings of the 2 columns are positionally related. Say they could be time and temperature values, with a string of time values in col1 and a string of related temps in col2. This is data from an external system that I have no control over, but must load the data into my system

    I need to write a select statement that will return the contents like so:

    1, 1, a
    1, 3, c
    1, 5, e
    1, 7, g
    2, 11, aa
    2, 13, ac
    2, 15, ae
    2, 17, ag

    Has anyone encountered such as this? Any clues or code snippets?

    Thanks for any ideas,

    sa

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What split function do you use? If it uses a tally table you could return the numbers and link on those.
    Not used but how about Kristen's here?
    http://www.sqlteam.com/forums/topic....48&whichpage=2
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    your "relationships" are based on offsets...so what value or meaning does that infer??????

    what the hell....use a cursor

    actually a simple loop and udf will do
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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