Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    2

    Unanswered: Parsing a ranges of numbers into a table

    Hi,

    I want to parse a string like '1,2,5,10,34-36,201' into a table of values holding these numbers. Essentially the result of a the relvant function would return a single column table containing the values 1,2,5,10,34,35,36,201.

    Thanks,
    DB

  2. #2
    Join Date
    May 2009
    Posts
    2

  3. #3
    Join Date
    May 2009
    Posts
    2
    Hi ergen,

    Thanks for the help.

    A few questions though. First of all, while waiting for an answer, being a programmer, I went ahead and solved it like a programmer would - using loops. Although this is a no brainer on small scale, if this were handling very large ranges of numbers, would the while be slower in SQL Server?

    Secondly, I'm still getting accustomed to SQL. It's possible I misunderstood the script, but It seems to me it would handle only numbers of up to 999. Did I get it right?

    Thanks,
    DB

  4. #4
    Join Date
    May 2009
    Posts
    2
    Loops are always slower. It should be forbidden The solution on the website can handle numbers up to 999 indeed but it is very easily expendable.
    Just add next join sections as I did below extending the range to max 99999.

    insert into #TEMP(Item)
    select p1.Digit+10*p2.Digit+100*p3.Digit+1000*p4.Digit+10 000*p5.Digit
    from #TEMP t
    inner join (select cast(0 as int) as Digit
    union select 1 union select 2
    union select 3 union select 4
    union select 5 union select 6
    union select 7 union select 8
    union select 9) as p1 on (1=1)
    inner join (select cast(0 as int) as Digit
    union select 1 union select 2
    union select 3 union select 4
    union select 5 union select 6
    union select 7 union select 8
    union select 9) as p2 on (1=1)
    inner join (select cast(0 as int) as Digit
    union select 1 union select 2
    union select 3 union select 4
    union select 5 union select 6
    union select 7 union select 8
    union select 9) as p3 on (1=1)
    inner join (select cast(0 as int) as Digit
    union select 1 union select 2
    union select 3 union select 4
    union select 5 union select 6
    union select 7 union select 8
    union select 9) as p4 on (1=1)
    inner join (select cast(0 as int) as Digit
    union select 1 union select 2
    union select 3 union select 4
    union select 5 union select 6
    union select 7 union select 8
    union select 9) as p5 on (1=1)
    where t.flgRange=1 and (
    t.Range_From<=(p1.Digit+10*p2.Digit+100*p3.Digit+1 000*p4.Digit+10000*p5.Digit)and
    t.Range_To>=(p1.Digit+10*p2.Digit+100*p3.Digit+100 0*p4.Digit+10000*p5.Digit)
    )
    order by t.Item,p1.Digit+10*p2.Digit+100*p3.Digit+1000*p4.D igit+10000*p5.Digit

Posting Permissions

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