TSQL Code code to generate range of numbers after spliting data having multiple delimiters

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 209
Let's say we have a data as '1,2,3,4-20,28,29,30'

We want the output as
Number
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
28
29
30


The below TSQL Script will do so

DECLARE @data VARCHAR(50) = '1,2,3,4-20,28,29,30'

;WITH segregateRecords AS(
SELECT
value
,CheckForRange = IIF(value Like '%-%','Generate Range','OK')
FROM STRING_SPLIT(@data,','))
,noRangeRecordsCTE AS(
SELECT value
FROM segregateRecords
WHERE CheckForRange = 'OK'
)
,rangeRecordsCTE AS(

SELECT
StartRange = PARSENAME(REPLACE(value, '-', '.'), 2)
,EndRange = PARSENAME(REPLACE(value, '-', '.'), 1)
FROM(
SELECT value
FROM segregateRecords
WHERE CheckForRange = 'Generate Range'
)X
)

SELECT DISTINCT Number = number
FROM master..spt_values
WHERE number BETWEEN (SELECT StartRange FROM rangeRecordsCTE) AND (SELECT EndRange FROM rangeRecordsCTE)
UNION ALL
SELECT *
FROM noRangeRecordsCTE
ORDER BY 1

Comments or Responses

Login to post response