Выберите строки, используя in с параметром строки, разделенным запятыми

Я конвертирую хранимую процедуру из MySql в SQL Server. Процедура имеет один входной параметр nvarchar/varchar который является разделенной запятой строкой, например

'1,2,5,456,454,343,3464'

Мне нужно написать запрос, который будет извлекать соответствующие строки, в MySql я использую FIND_IN_SET и мне интересно, что эквивалент в SQL Server.

Мне также нужно заказать идентификаторы, как в строке.

Исходный запрос:

SELECT * 
 FROM table_name t
 WHERE FIND_IN_SET(id,p_ids)
 ORDER BY FIND_IN_SET(id,p_ids);
3 ответа

Эквивалент like для where а затем charindex() для order by:

select *
from table_name t
where ','+p_ids+',' like '%,'+cast(id as varchar(255))+',%'
order by charindex(',' + cast(id as varchar(255)) + ',', ',' + p_ids + ',');

Ну, вы можете использовать charindex() для обоих, но like будет работать в большинстве баз данных.

Обратите внимание, что я добавил разделители в начало и конец строки, поэтому 464 случайно не будет соответствовать 3464.


Вам нужно будет написать функцию FIND_IN_SET, поскольку она не существует. Механизм шкафа, который я могу придумать для преобразования строки с разделителями в объект, соединяющий объект, будет состоять в том, чтобы создать табличную функцию и использовать результат в стандартном выражении. Это должно быть похоже на:

DECLARE @MyParam NVARCHAR(3000)

SET @MyParam='1,2,5,456,454,343,3464'

SELECT 
 * 
FROM 
 MyTable 
WHERE 
 MyTableID IN (SELECT ID FROM dbo.MySplitDelimitedString(@MyParam,','))

И вам нужно будет создать MySplitDelimitedString типа табличное значение функции, которая бы разделить строку и возвращает TABLE (ID INT) объект.


Решение на основе набора, которое разбивает id на int и соединяется с базовой таблицей, которая будет использовать индекс для идентификатора базовой таблицы. Я предположил, что id будет int, иначе просто удалите бросок.

declare @ids nvarchar(100) = N'1,2,5,456,454,343,3464';

with nums as ( -- Generate numbers
 select top (len(@ids)) row_number() over (order by (select 0)) n
 from sys.messages
)
, pos1 as ( -- Get comma positions
 select c.ci
 from nums n
 cross apply (select charindex(',', @ids, n.n) as ci) c
 group by c.ci
)
, pos2 as ( -- Distinct posistions plus start and end
 select ci
 from pos1
 union select 0
 union select len(@ids) + 1
)
, pos3 as ( -- add row number for join
 select ci, row_number() over (order by ci) as r
 from pos2
)
, ids as ( -- id and row id for ordering
 select cast(substring(@ids, p1.ci + 1, p2.ci - p1.ci - 1) as int) id, row_number() over (order by p1.ci) r
 from pos3 p1
 inner join pos3 p2 on p2.r = p1.r + 1
)
select *
from ids i
 inner join table_name t on t.id = i.id
order by i.r;

licensed under cc by-sa 3.0 with attribution.