只要您的时间间隔小于2048(如果我知道数字是否可以增加),并且您@data遵循当前语法,此方法就可以工作:
declare @data varchar(50) = '1,2,3,4-8,10,11'
;with x as
(
SELECT t.c.value('.', 'VARCHAR(2000)') subrow
FROM (
SELECT x = CAST('<t>' +
REPLACE(@data, ',', '</t><t>') + '</t>' AS XML)
) a
CROSS APPLY x.nodes('/t') t(c)
), y as
(
SELECT
CAST(coalesce(PARSENAME(REPLACE(subrow, '-', '.'), 2),
PARSENAME(REPLACE(subrow, '-', '.'), 1)) as int) f,
CAST(PARSENAME(REPLACE(subrow, '-', '.'), 1) as int) t from x
)
select z.number from y
cross apply
(select y.f + number number
from master..spt_values
where number <= y.t - y.f and type = 'p'
) z
结果:
1
2
3
4
5
6
7
8
10
11