Query Split string into rows

Question

I have a table that looks like this:

ID    Value
1     1,10
2     7,9

I want my result to look like this:

ID   Value
1    1
1    2
1    3
1    4
1    5
1    6
1    7
1    8
1    9
1    10
2    7
2    8
2    9

I'm after both a range between 2 numbers with , as the delimiter (there can only be one delimiter in the value) and how to split this into rows.

Answer 1

Any table that a field with multiple values such as this is a problem in terms of design. The only way to deal with these records as it is is to split the values on the delimiter and put them into a temporary table, implement custom splitting code, integrate a CTE as noted, or redesign the original table to put the comma-delimited fields into separate fields, eg

ID LOWLIMIT HILIMIT
1  1        10

Answer 2

Splitting the comma separated numbers is a small part of this problem. The parsing should be done in the application and the range stored in separate columns. For more than one reason: Storing numbers as strings is a bad idea. Storing two attributes in a single column is a bad idea. And, actually, storing unsanitized user input in the database is also often a bad idea.

In any case, one way to generate the list of numbers is to use a recursive CTE:

with t as (
      select t.*, cast(left(value, charindex(',', value) - 1) as int) as first,
             cast(substring(value, charindex(',', value) + 1, 100) as int) as last
      from table t
     ),
     cte as (
      select t.id, t.first as value, t.last
      from t
      union all
      select cte.id, cte.value + 1, cte.last
      from cte
      where cte.value < cte.last
     )
select id, value
from cte
order by id, value;

You may need to fiddle with the value of MAXRECURSION if the ranges are really big.

Answer 3

similar with Gordon Linoff variant, but has some difference

--create temp table for data sample
DECLARE @Yourdata AS TABLE ( id INT, VALUE VARCHAR(20) )
INSERT  @Yourdata
        ( id, VALUE )
VALUES  ( 1, '1,10' ),
        ( 2, '7,9' )
--final query
;WITH    Tally
          AS ( SELECT   MIN(CONVERT(INT, SUBSTRING(y.VALUE, 1, CHARINDEX(',', y.value) - 1))) AS MinV ,
                        MAX(CONVERT(INT, SUBSTRING(y.VALUE, CHARINDEX(',', y.value) + 1, 18))) AS MaxV
               FROM     @yourdata AS y
               UNION ALL
               SELECT   MinV = MinV + 1 , MaxV
               FROM     Tally
               WHERE    MinV < Maxv
             )
    SELECT  y.id , t.minV AS value
    FROM    @yourdata AS y
            JOIN tally AS t ON t.MinV BETWEEN CONVERT(INT, SUBSTRING(y.VALUE, 1, CHARINDEX(',', y.value) - 1))
                                      AND     CONVERT(INT, SUBSTRING(y.VALUE, CHARINDEX(',', y.value) + 1, 18))
    ORDER BY id, minV
OPTION  ( MAXRECURSION 999 ) --change it if required

output