Quick amendment to the code, only unpacking the hyphened serial values
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SerialNumber VARCHAR(1024) = '1000-1010,1230,1234,1235,1236,1239,1244,1250-1260';
-- Return only the requested serial numbers
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@SerialNumber),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@SerialNumber,t.N,1) = ','
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(',',@SerialNumber,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
--INSERT INTO @SerialTable (Serial)
,BASE_SERIAL AS
(
SELECT
SUBSTRING(@SerialNumber, l.N1, l.L1) AS ISerial
FROM cteLen l
)
,SERIALS_TO_UNPACK AS
(
SELECT
BS.ISerial
,CONVERT(INT,SUBSTRING(BS.ISerial,1,CHARINDEX('-',BS.ISerial) -1),0) AS S_FROM
,CONVERT(INT,SUBSTRING(BS.ISerial,CHARINDEX('-',BS.ISerial) + 1,LEN(BS.ISerial)),0) AS S_TO
FROM BASE_SERIAL BS
WHERE CHARINDEX('-',BS.ISerial,1) > 1
)
,T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
SELECT
STU.S_FROM + X.N
FROM SERIALS_TO_UNPACK STU
OUTER APPLY
(
SELECT TOP((STU.S_TO - STU.S_FROM) + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T,T T9
) AS X
UNION ALL
SELECT
CONVERT(INT,BS.ISerial,0)
FROM BASE_SERIAL BS
WHERE CHARINDEX('-',BS.ISerial,1) = 0
ORDER BY 1;
Results
----
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1230
1234
1235
1236
1239
1244
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260