Search list of values including range in SQL using

问题:

I am trying to implement search functionality with list of values in SQL variable, including range. Appreciate any guidance/links pointing to correct approach for this.

Below is the dataset:

CREATE TABLE [dbo].[Books]
(
    [ID] [NCHAR](10) NOT NULL,
    [AUTHCODE] [NCHAR](10) NULL,
    [TITLE] [NCHAR](10) NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Books] ([ID], [AUTHCODE], [TITLE]) 
VALUES (N'1', N'nk', N'Book1'), 
       (N'2', N'an', N'Book2'),
       (N'3', N'mn', N'Book3'),
       (N'4', N'ra', N'Book4'),
       (N'5', N'kd', N'Book5'),
       (N'6', N'nk', N'Book6'),
       (N'7', N'an', N'Book7'),
       (N'8', N'ra', N'Book8'),
       (N'9', N'kd', N'Book9'),
       (N'10', N'mn', N'Book10    ')
GO

Below I am trying to filter using the SQL IN clause but this does not return desired result.

select * from books

declare @List1 varchar(max) = '2,4,6,7,8,9' --simple list

select * 
from books
where id in (@List1)

declare @List2 varchar(max) = '2,4-7,9' --list with range

select * 
from books
where id in (@List2)

回答1:

You cannot directly use strings as lists, but you can do use STRING_SPLIT (Transact-SQL) if you really need to pass filtering parameters as strings:

declare @list varchar(max) = '2,4,6-8,9'
declare @filter table (id1 int, id2 int)

insert into @filter (id1,id2)
select
    case when b.pos > 0 then left(a.[value], pos - 1) else a.[value] end as id1,
    case when b.pos > 0 then right(a.[value], len(a.[value]) - pos) else a.[value] end as id2
from string_split(@list, ',') as a
    cross apply (select charindex('-', a.[value]) as pos) as b

select *
from [dbo].[Books] as b
where
    exists (select * from @filter as tt where b.id between tt.id1 and tt.id2)

Also it might be an idea to pass your filter as json and OPENJSON (Transact-SQL) so you can make parsing part simplier:

declare @list varchar(max) = '[2,4,[6,8],9]'

select
    case when a.[type] = 4 then json_value(a.[value], '$[0]') else a.[value] end,
    case when a.[type] = 4 then json_value(a.[value], '$[1]') else a.[value] end
from openjson(@list) as a

All above, of course, only applicable if you have Sql Server 2016 or higher

回答2:

IN() operator determines whether a specified value matches any value in a subquery or a list. not a string and that what you are doing.

What you are trying to do can be done as

DECLARE @List1 AS TABLE (ID INT); 

INSERT INTO @List1 
SELECT 2 UNION SELECT 4 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9;

SELECT *
FROM Books
WHERE ID IN (SELECT ID FROM @List1);

DECLARE @List2 As TABLE (AFrom INT, ATo INT);

INSERT INTO @List2
SELECT 2, 4 UNION SELECT 7, 9;

SELECT *
FROM Books B CROSS APPLY @List2 L
WHERE B.ID BETWEEN L.AFrom AND L.ATo;

Live Demo