sql - Query Split string into rows -

i have table looks this:

id    value 1     1,10 2     7,9 

i want result 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 range between 2 numbers , delimiter (there can 1 delimiter in value) , how split rows.

splitting comma separated numbers small part of problem. parsing should done in application , range stored in separate columns. more 1 reason: storing numbers strings bad idea. storing 2 attributes in single column bad idea. and, actually, storing unsanitized user input in database bad idea.

in case, 1 way generate list of numbers use recursive cte:

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

you may need fiddle value of maxrecursion if ranges big.