Quick solution (if I got this right, only on the first coffee in the morning:-) ), using another Tally table to generate the missing numbers.
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SerialNumber VARCHAR(1024) = '1230,1234,1235,1236,1239,1244';
-- 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)
,INT_SERIAL AS
(
SELECT
CONVERT(INT,SUBSTRING(@SerialNumber, l.N1, l.L1),0) AS ISerial
FROM cteLen l
)
,T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,START_AND_COUNT AS
(
SELECT
MIN(ISE.ISerial) AS START_VALUE
,MAX(ISE.ISerial) - MIN(ISE.ISerial) AS COUNT_VALUE
FROM INT_SERIAL ISE
)
,FULL_SERIAL(SERIAL) AS (SELECT TOP(SELECT COUNT_VALUE + 1 FROM START_AND_COUNT) ROW_NUMBER()
OVER (ORDER BY (SELECT NULL)) + SC.START_VALUE -1 AS SERIAL
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T,T T9
OUTER APPLY START_AND_COUNT SC
)
SELECT
FS.SERIAL
FROM FULL_SERIAL FS;
Results
SERIAL
-------
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244