This blog was inspired by the following thread at MSDN Transact-SQL forum:
Multiple values in field as parameter
Given a string with values such as ‘201|203|301|400..600|725|800..900’ return records from the table where Code field will be any of the values passed with | as a delimiter and also within the passed ranges where range indication will be ..
The first idea that comes to mind is to use various splitting techniques available (see, for example, this excellent blog by Aaron Bertrand Splitting List of Integers or follow up by Brad Schulz Integer List Split – a SQL fable or the old times classic by Erland Sommarskog Arrays and Lists in SQL Server) split the list first by | and then, if needed by .. to introduce the ranges of values.
In order to run the solution I implemented, we need to create a Numbers table in our database first (why it’s important you can read in this classical article
Why should I consider using an auxiliary Calendar table).
Here is a script I use to create both Numbers and Calendar tables once in the Model database so all other user databases will inherit them:
USE model
GO
CREATE TABLE dbo.numbers (
number int NOT NULL
)
ALTER TABLE dbo.numbers
ADD
CONSTRAINT pk_numbers PRIMARY KEY CLUSTERED (number)
WITH FILLFACTOR = 100
GO
INSERT INTO dbo.numbers (number)
SELECT (a.number * 256) + b.number As number
FROM (
SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number <= 255
) As a
CROSS
JOIN (
SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number <= 255
) As b
GO
CREATE TABLE dbo.calendar (
the_date datetime NOT NULL
, is_monday bit NOT NULL
, is_tuesday bit NOT NULL
, is_wednesday bit NOT NULL
, is_thursday bit NOT NULL
, is_friday bit NOT NULL
, is_saturday bit NOT NULL
, is_sunday bit NOT NULL
, is_weekend As (is_saturday ^ is_sunday)
, is_holiday bit
, holiday_desc varchar(50)
)
GO
ALTER TABLE dbo.calendar
ADD
CONSTRAINT pk_calendar PRIMARY KEY CLUSTERED (the_date)
WITH FILLFACTOR = 100
GO
INSERT INTO dbo.calendar (the_date, is_monday, is_tuesday, is_wednesday, is_thursday, is_friday, is_saturday, is_sunday)
SELECT the_date
, CASE WHEN DateDiff(dd, 0, the_date) % 7 = 0 THEN 1 ELSE 0 END As is_monday
, CASE WHEN DateDiff(dd, 0, the_date) % 7 = 1 THEN 1 ELSE 0 END As is_tuesday
, CASE WHEN DateDiff(dd, 0, the_date) % 7 = 2 THEN 1 ELSE 0 END As is_wednesday
, CASE WHEN DateDiff(dd, 0, the_date) % 7 = 3 THEN 1 ELSE 0 END As is_thursday
, CASE WHEN DateDiff(dd, 0, the_date) % 7 = 4 THEN 1 ELSE 0 END As is_friday
, CASE WHEN DateDiff(dd, 0, the_date) % 7 = 5 THEN 1 ELSE 0 END As is_saturday
, CASE WHEN DateDiff(dd, 0, the_date) % 7 = 6 THEN 1 ELSE 0 END As is_sunday
FROM (
SELECT DateAdd(dd, number, 0) As the_date
FROM dbo.numbers
) As dates
Now, we need to create a function that will split the values. I chose the Numbers table variation of this function:
CREATE FUNCTION [dbo].[fnSplit2](
@List VARCHAR(MAX),
@Delimiter VARCHAR(10)
)
RETURNS TABLE
AS
RETURN
(
SELECT ROW_NUMBER() over (order by Number) as Pos,
Value = LTRIM(RTRIM(
SUBSTRING(@List, Number,
CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
FROM
dbo.Numbers WITH (NOLOCK)
WHERE
Number <= CONVERT(INT, LEN(@List))
AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
);
GO
And now we can create our final solution:
use AdventureWorks
--select * from Sales.SalesOrderHeader order by CustomerID
declare @Param varchar(max)
set @Param = '1|2|5|10..20|43|70..86|102'
declare @t table (iPos int, cValue varchar(max))
insert into @t select * from dbo.fnSplit2(@Param, '|')
if not exists (select 1 from @t where cValue like '%..%') -- we don't have to split by ..
select distinct S.CustomerID from Sales.SalesOrderHeader S inner join @t T on S.CustomerID = T.cValue
ORDER BY S.CustomerID
else
begin
-- we need to split by .. now
if OBJECT_ID('TempDB..#TempRanges','U') IS NOT NULL
drop table #TempRanges
-- select * from @t
select iPos, cValue, max(case when Pos = 1 then Value end) as MinVal,
max(case when Pos = 2 then Value end) as MaxVal into #TempRanges
from (
select T.*, F.Pos, F.Value from @t T OUTER apply dbo.fnSplit2(T.cValue, '..') F ) X
GROUP BY iPos, cValue
--select * from #TempRanges
--select T1.CustomerID from Sales.SalesOrderHeader T1 where exists
--(select 1 from #TempRanges X where cValue not like '%..%' and T1.CustomerID = X.cValue)
--union
--select T.CustomerID from Sales.SalesOrderHeader T
--inner join #TempRanges X on
--T.CustomerID between X.MinVal and X.MaxVal and X.cValue LIKE '%..%'
--ORDER BY CustomerID
select distinct T.CustomerID from Sales.SalesOrderHeader T
where exists (select 1 from #TempRanges X where
T.CustomerID between X.MinVal and coalesce(X.MaxVal, X.MinVal))
ORDER BY CustomerID
end
There is a simpler XML based solution presented by Peso (Peter Larsson) in the following MSDN Thread:
DECLARE @Sample TABLE
(
ID INT,
Data VARCHAR(100)
)
INSERT @Sample
VALUES (1, '1000|5000')
DECLARE @ID INT = 1
-- Solution here
;WITH cteSource(ID, Data)
AS (
SELECT ID,
CAST('<v><i>' + REPLACE(REPLACE(Data, '|', '</i></v><v><i>'), '..', '</i><i>') + '</i></v>' AS XML) AS Data
FROM @Sample
WHERE ID = @ID
)
SELECT s.ID,
v.value('i[1]', 'INT') AS [Start],
COALESCE(v.value('i[2]', 'INT'), v.value('i[1]', 'INT')) AS [End]
FROM cteSource AS s
CROSS APPLY Data.nodes('v') AS n(v)
*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
