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:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
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
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:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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
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:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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
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:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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)
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