September 15, 2014 at 12:55 pm
Given a single parameter, @SerialNumbers
AND
Given a table with field SerialNumber with data type string
The @SerialNumbers parameter is a text entry where the following are valid entries:
Return Rows where SerialNumber is 1234 through 1240
1234,1235,1236,1237,1238,1239,1240
Return Rows where SerialNumber is 1234, 1236, 1250 through 1260, 1267, 1270 through 1299
1234,1236,1250-1260,1267,1270-1299
I'm able to extract the values between the commas using a Tally table method, as described here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
But, taking it to the next level and handling the values that are "ranges"... I'm not sure how to do this.
Here's what I have so far:
-- 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)
SELECT Serial = SUBSTRING(@SerialNumber, l.N1, l.L1)
FROM cteLen l
The code above create a table @SerialTable and populates it with the values specified as comma separated values from @SerialNumbers
So, with the above code and given @SerialNumbers=1234,1236,1250-1260,1267,1270-1299, @SerialTable would contain rows as follows:
1234
1236
1250-1260
1267
1270-1299
If I wanted to update @SerialTable to breakout rows 1250-1260 and 1270-1299 with the actual values, what's the most straightforward way to do this? Hopefully I don't need to use a for loop...
September 16, 2014 at 10:04 pm
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
September 17, 2014 at 7:56 am
Thanks for responding, Eirikur. I've been racking my brain over this for a couple weeks now. Looking at the code, I don't think it will do what I need.
Given @SerialNumber = '6100,6102,6106,6107-6117,6131,6156,6200-6299,6451,6006,5990-5999,6001'
Output should be:
Serial
====
6100
6102
6106
6107
6108
6109
6110
6111
6112
6113
6114
6115
6116
6117
6131
6156
6200
6201
6202
...
6297
6298
6299
6451
6006
5990
5991
5992
...
5997
5998
5999
6001
As of now, my code will output the following:
Serial
====
6100
6102
6106
6107-6117
6131
6156
6200-6299
6451
6006
5990-5999
6001
I should add that this is being used as a filter to select existing records. The @SerialTable is JOINed to the actual table that I'm trying to filter from. So, I'm thinking for the second part, I take any rows from @SerialTable which has a '-' in the Serial field, put together a query like "SELECT t.SerialNumber FROM DataTable t WHERE (t.SerialNumber BETWEEN 6107 AND 6117) OR (t.SerialNumber BETWEEN 6200 AND 6299) OR (t.SerialNumber BETWEEN 5990 AND 5999)". Then I would need to remove any rows in @SerialTable which contains '-'. I just don't know how I should write the SQL to take those rows from @SerialTable and build the WHERE clause for it. I am able to use Dynamic SQL, and I think that's the easiest way.
Hope this helps.
September 19, 2014 at 12:15 pm
The final solution ended up being fairly straightforward.
Given that I was able to split the @SerialNumbers parameter on the commas into a temporary table, the next step was to find any values in my data table that fell between any two values specified as a range in the parameter. I did this by adding an additional INSERT INTO query:
-- Find any serials for which a RANGE of values was specified
INSERT INTO @SerialTable (Serial)
SELECT DISTINCT td.SerialNumber
FROM TestData td
INNER JOIN @SerialTable st ON
SUBSTRING(st.Serial, 1, CHARINDEX('-',st.Serial,1) - 1) <= td.SerialNumber AND --(Get the MIN value from the range)
RIGHT(st.Serial, CHARINDEX('-',REVERSE(st.Serial),1) - 1) >= td.SerialNumber --(Get the MAX value from the range)
WHERE st.Serial LIKE '%-%'; -- only select rows from @SerialTable which contain the range character (hyphen)
-- Delete the RANGE values from the SerialTable (OPTIONAL)
-- DELETE FROM @SerialTable WHERE Serial LIKE '%-%';
September 21, 2014 at 5:33 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply