Передача varchar, полного значений, разделенных запятыми, в функцию SQL Server IN
Дубликат
Динамический SQL-запрос с разделителями-запятыми
Параметризованные запросы с Like и In
У меня есть хранимая процедура SQL Server, где я хотел бы передать varchar полный значений, разделенных запятыми IN функция. Например:
DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';
SELECT *
FROM sometable
WHERE tableid IN (@Ids);
Это не работает, конечно. Я получаю ошибку:
Ошибка преобразования при преобразовании значения varchar '1,2,3,5,4,6,7,98,234' в тип данных int.
Как я могу сделать это (или что-то относительно похожее), не прибегая к созданию динамического SQL?
29 ответов
Не используйте функцию, которая зацикливается на разбиение строки!, моя функция ниже будет разбивать строку очень быстро, без зацикливания!
Прежде чем использовать мою функцию, вам нужно настроить таблицу "помощник", вам нужно сделать это только один раз для каждой базы данных:
CREATE TABLE Numbers
(Number int NOT NULL,
CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
SET @x=@x+1
INSERT INTO Numbers VALUES (@x)
END
используйте эту функцию, чтобы разбить вашу строку, которая не зацикливается и очень быстро:
CREATE FUNCTION [dbo].[FN_ListToTable]
(
@SplitOn char(1) --REQUIRED, the character to split the @List string on
,@List varchar(8000) --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
ListValue varchar(500)
)
AS
BEGIN
/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.
Returns a table, one row per item in the list, with a column name "ListValue"
EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')
returns:
ListValue
-----------
1
12
123
1234
54321
6
A
*
|||
B
(10 row(s) affected)
**/
----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
(ListValue)
SELECT
ListValue
FROM (SELECT
LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
FROM (
SELECT @SplitOn + @List + @SplitOn AS List2
) AS dt
INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
WHERE SUBSTRING(List2, number, 1) = @SplitOn
) dt2
WHERE ListValue IS NOT NULL AND ListValue!=''
RETURN
END --Function FN_ListToTable
Вы можете использовать эту функцию в качестве таблицы в соединении:
SELECT
Col1, COl2, Col3...
FROM YourTable
INNER JOIN FN_ListToTable(',',@YourString) s ON YourTable.ID = s.ListValue
Вот ваш пример:
Select * from sometable where tableid in(SELECT ListValue FROM dbo.FN_ListToTable(',',@Ids) s)
Конечно, если вы ленивы, как я, вы можете просто сделать это:
Declare @Ids varchar(50) Set @Ids = ',1,2,3,5,4,6,7,98,234,'
Select * from sometable
where Charindex(','+cast(tableid as varchar(8000))+',', @Ids) > 0
Нет таблицы Нет функции Нет цикла
Основываясь на идее разбора вашего списка в таблицу, наш администратор базы данных предложил использовать XML.
Declare @Ids varchar(50)
Set @Ids = ‘1,2,3,5,4,6,7,98,234’
DECLARE @XML XML
SET @XML = CAST('<i>' + REPLACE(@Ids, ',', '</i><i>') + '</i>' AS XML)
SELECT *
FROM
SomeTable
INNER JOIN @XML.nodes('i') x(i)
ON SomeTable .Id = x.i.value('.', 'VARCHAR(MAX)')
Похоже, они имеют ту же производительность, что и ответ @KM, но, думаю, намного проще.
Вы можете создать функцию, которая возвращает таблицу.
так что ваше заявление будет что-то вроде
select * from someable
join Splitfunction(@ids) as splits on sometable.id = splits.id
Вот симуляционная функция.
CREATE FUNCTION [dbo].[FUNC_SplitOrderIDs]
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
RETURN
END
Это очень распространенный вопрос. Консервированный ответ, несколько приятных приемов:
Это работает отлично! Приведенные ниже ответы слишком сложны. Не смотрите на это как на динамику. Настройте процедуру вашего магазина следующим образом:
(@id as varchar(50))
as
Declare @query as nvarchar(max)
set @query ='
select * from table
where id in('+@id+')'
EXECUTE sp_executesql @query
Я думаю, что очень простое решение может быть следующим:
DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';
SELECT *
FROM sometable
WHERE ','+@Ids+',' LIKE '%,'+CONVERT(VARCHAR(50),tableid)+',%';
Я могу предложить использовать WITH как это:
DECLARE @Delim char(1) = ',';
SET @Ids = @Ids + @Delim;
WITH CTE(i, ls, id) AS (
SELECT 1, CHARINDEX(@Delim, @Ids, 1), SUBSTRING(@Ids, 1, CHARINDEX(@Delim, @Ids, 1) - 1)
UNION ALL
SELECT i + 1, CHARINDEX(@Delim, @Ids, ls + 1), SUBSTRING(@Ids, ls + 1, CHARINDEX(@Delim, @Ids, ls + 1) - CHARINDEX(@Delim, @Ids, ls) - 1)
FROM CTE
WHERE CHARINDEX(@Delim, @Ids, ls + 1) > 1
)
SELECT t.*
FROM yourTable t
INNER JOIN
CTE c
ON t.id = c.id;
Без использования динамического SQL вы должны взять входную переменную и использовать функцию split, чтобы поместить данные во временную таблицу и затем присоединиться к ней.
Тонны ответов здесь, но я думаю, что добавить свои два цента
STRING_SPLIT это очень простой подход к такого рода проблемам:
DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';
SELECT *
FROM sometable
WHERE tableid IN;
(SELECT value FROM STRING_SPLIT(@Ids, ','))
Создайте табличную функцию, подобную приведенной ниже, которая разбирает запятую varchar и возвращает таблицу, которая может быть внутренне объединена с другими таблицами.
CREATE FUNCTION [dbo].[fn_SplitList]
(
@inString varchar(MAX) = '',
@inDelimiter char(1) = ',' -- Keep the delimiter to 100 chars or less. Generally a delimiter will be 1-2 chars only.
)
RETURNS @tbl_Return table
(
Unit varchar(1000) COLLATE Latin1_General_BIN
)
AS
BEGIN
INSERT INTO @tbl_Return
SELECT DISTINCT
LTRIM(RTRIM(piece.value('./text()[1]', 'varchar(1000)'))) COLLATE DATABASE_DEFAULT AS Unit
FROM
(
--
-- Replace any delimiters in the string with the "X" tag.
--
SELECT
CAST(('<X>' + REPLACE(s0.prsString, s0.prsSplitDelimit, '</X><X>') + '</X>') AS xml).query('.') AS units
FROM
(
--
-- Convert the string and delimiter into XML.
--
SELECT
(SELECT @inString FOR XML PATH('')) AS prsString,
(SELECT @inDelimiter FOR XML PATH('')) AS prsSplitDelimit
) AS s0
) AS s1
CROSS APPLY units.nodes('X') x(piece)
RETURN
END
======================================================= Теперь потребляя выше созданную табличную функцию в вашем коде, создание функции - это однократное действие в вашей базе данных, которое может использоваться в разных базах данных, а также на одном сервере.
DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';
SELECT
*
FROM sometable AS st
INNER JOIN fn_SplitList(@ids, ',') AS sl
ON sl.unit = st.tableid
Если вы используете SQL Server 2008 или выше, используйте параметры с табличным значением; например:
CREATE PROCEDURE [dbo].[GetAccounts](@accountIds nvarchar)
AS
BEGIN
SELECT *
FROM accountsTable
WHERE accountId IN (select * from @accountIds)
END
CREATE TYPE intListTableType AS TABLE (n int NOT NULL)
DECLARE @tvp intListTableType
-- inserts each id to one row in the tvp table
INSERT @tvp(n) VALUES (16509),(16685),(46173),(42925),(46167),(5511)
EXEC GetAccounts @tvp
Спасибо, за вашу функцию я использовал это........................ Это мой пример
**UPDATE [RD].[PurchaseOrderHeader]
SET [DispatchCycleNumber] ='10'
WHERE OrderNumber in(select * FROM XA.fn_SplitOrderIDs(@InvoiceNumberList))**
CREATE FUNCTION [XA].[fn_SplitOrderIDs]
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
RETURN
END
Это было какое-то время, но я делал это раньше, используя XML как промежуточный.
Я не могу поверить в это, но боюсь, я больше не знаю, откуда у меня появилась эта идея:
-- declare the variables needed
DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
-- The string you want to split
SET @str='A,B,C,D,E,Bert,Ernie,1,2,3,4,5'
-- What you want to split on. Can be a single character or a string
SET @delimiter =','
-- Convert it to an XML document
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
-- Select back from the XML
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)
Попробуй это:
SELECT ProductId, Name, Tags
FROM Product
WHERE '1,2,3,' LIKE '%' + CAST(ProductId AS VARCHAR(20)) + ',%';
Как сказано в последнем примере по этой ссылке
У меня та же идея с пользователем KM. но не нужно дополнительного номера таблицы. Только эта функция.
CREATE FUNCTION [dbo].[FN_ListToTable]
(
@SplitOn char(1) --REQUIRED, the character to split the @List string on
,@List varchar(8000) --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
ListValue varchar(500)
)
AS
BEGIN
DECLARE @number int = 0
DECLARE @childString varchar(502) = ''
DECLARE @lengthChildString int = 0
DECLARE @processString varchar(502) = @SplitOn + @List + @SplitOn
WHILE @number < LEN(@processString)
BEGIN
SET @number = @number + 1
SET @lengthChildString = CHARINDEX(@SplitOn, @processString, @number + 1) - @number - 1
IF @lengthChildString > 0
BEGIN
SET @childString = LTRIM(RTRIM(SUBSTRING(@processString, @number + 1, @lengthChildString)))
IF @childString IS NOT NULL AND @childString != ''
BEGIN
INSERT INTO @ParsedList(ListValue) VALUES (@childString)
SET @number = @number + @lengthChildString - 1
END
END
END
RETURN
END
А вот и тест:
SELECT ListValue FROM dbo.FN_ListToTable('/','a/////bb/c')
Результат:
ListValue
______________________
a
bb
c
Я написал хранимую процедуру, чтобы показать, как это сделать раньше. Вы в основном должны обработать строку. Я попытался опубликовать код здесь, но форматирование получилось не совсем понятным.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[uspSplitTextList]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[uspSplitTextList]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
-- uspSplitTextList
--
-- Description:
-- splits a separated list of text items and returns the text items
--
-- Arguments:
-- @list_text - list of text items
-- @Delimiter - delimiter
--
-- Notes:
-- 02/22/2006 - WSR : use DATALENGTH instead of LEN throughout because LEN doesn't count trailing blanks
--
-- History:
-- 02/22/2006 - WSR : revised algorithm to account for items crossing 8000 character boundary
-- 09/18/2006 - WSR : added to this project
--
CREATE PROCEDURE uspSplitTextList
@list_text text,
@Delimiter varchar(3)
AS
SET NOCOUNT ON
DECLARE @InputLen integer -- input text length
DECLARE @TextPos integer -- current position within input text
DECLARE @Chunk varchar(8000) -- chunk within input text
DECLARE @ChunkPos integer -- current position within chunk
DECLARE @DelimPos integer -- position of delimiter
DECLARE @ChunkLen integer -- chunk length
DECLARE @DelimLen integer -- delimiter length
DECLARE @ItemBegPos integer -- item starting position in text
DECLARE @ItemOrder integer -- item order in list
DECLARE @DelimChar varchar(1) -- first character of delimiter (simple delimiter)
-- create table to hold list items
-- actually their positions because we may want to scrub this list eliminating bad entries before substring is applied
CREATE TABLE #list_items ( item_order integer, item_begpos integer, item_endpos integer )
-- process list
IF @list_text IS NOT NULL
BEGIN
-- initialize
SET @InputLen = DATALENGTH(@list_text)
SET @TextPos = 1
SET @DelimChar = SUBSTRING(@Delimiter, 1, 1)
SET @DelimLen = DATALENGTH(@Delimiter)
SET @ItemBegPos = 1
SET @ItemOrder = 1
SET @ChunkLen = 1
-- cycle through input processing chunks
WHILE @TextPos <= @InputLen AND @ChunkLen <> 0
BEGIN
-- get current chunk
SET @Chunk = SUBSTRING(@list_text, @TextPos, 8000)
-- setup initial variable values
SET @ChunkPos = 1
SET @ChunkLen = DATALENGTH(@Chunk)
SET @DelimPos = CHARINDEX(@DelimChar, @Chunk, @ChunkPos)
-- loop over the chunk, until the last delimiter
WHILE @ChunkPos <= @ChunkLen AND @DelimPos <> 0
BEGIN
-- see if this is a full delimiter
IF SUBSTRING(@list_text, (@TextPos + @DelimPos - 1), @DelimLen) = @Delimiter
BEGIN
-- insert position
INSERT INTO #list_items (item_order, item_begpos, item_endpos)
VALUES (@ItemOrder, @ItemBegPos, (@TextPos + @DelimPos - 1) - 1)
-- adjust positions
SET @ItemOrder = @ItemOrder + 1
SET @ItemBegPos = (@TextPos + @DelimPos - 1) + @DelimLen
SET @ChunkPos = @DelimPos + @DelimLen
END
ELSE
BEGIN
-- adjust positions
SET @ChunkPos = @DelimPos + 1
END
-- find next delimiter
SET @DelimPos = CHARINDEX(@DelimChar, @Chunk, @ChunkPos)
END
-- adjust positions
SET @TextPos = @TextPos + @ChunkLen
END
-- handle last item
IF @ItemBegPos <= @InputLen
BEGIN
-- insert position
INSERT INTO #list_items (item_order, item_begpos, item_endpos)
VALUES (@ItemOrder, @ItemBegPos, @InputLen)
END
-- delete the bad items
DELETE FROM #list_items
WHERE item_endpos < item_begpos
-- return list items
SELECT SUBSTRING(@list_text, item_begpos, (item_endpos - item_begpos + 1)) AS item_text, item_order, item_begpos, item_endpos
FROM #list_items
ORDER BY item_order
END
DROP TABLE #list_items
RETURN
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Вы можете сделать это так:
create or replace
PROCEDURE UDP_SETBOOKMARK
(
P_USERID IN VARCHAR2
, P_BOOKMARK IN VARCHAR2
) AS
BEGIN
UPDATE T_ER_Bewertung
SET LESEZEICHEN = P_BOOKMARK
WHERE STAMM_ID in( select regexp_substr(P_USERID,'[^,]+', 1, level) from dual
connect by regexp_substr(P_USERID, '[^,]+', 1, level) is not null )
and ER_ID = (select max(ER_ID) from T_ER_Bewertung_Kopie);
commit;
END UDP_SETBOOKMARK;
Тогда попробуйте это с
Begin
UDP_SETBOOKMARK ('1,2,3,4,5', 'Test');
End;
Вы можете использовать это IN-Clause с regexp_substr и в других ситуациях, просто попробуйте.
-- select * from dbo.Split_ID('77,106')
ALTER FUNCTION dbo.Split_ID(@String varchar(8000))
returns @temptable TABLE (ID varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
declare @Delimiter char(1)
set @Delimiter =','
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(ID) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
Error 493: The column 'i' that was returned from the nodes() method cannot be
used directly. It can only be used with one of the four XML data type
methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT
NULL checks.
Вышеупомянутая ошибка была исправлена в SQL Server 2014 с помощью следующего фрагмента
Declare @Ids varchar(50)
Set @Ids = '1,2,3,5,4,6,7,98,234'
DECLARE @XML XML
SET @XML = CAST('<i>' + REPLACE(@Ids, ',', '</i><i>') + '</i>' AS XML)
SELECT SomeTable.*
FROM
SomeTable
cross apply @XML.nodes('i') x(i)
where SomeTable .Id = x.i.value('.', 'VARCHAR(MAX)')
Как сказал Мэтт выше, сделал это, как показано ниже (немного исправил), и это работает:
DECLARE @XML XML
Declare @Ids VARCHAR(MAX)='1001,1002,1003,1004'
SET @XML = CAST('<i>' + REPLACE(@Ids, ',', '</i><i>') + '</i>' AS XML)
--SELECT @XML
SELECT x.i.value('.', 'VARCHAR(MAX)') Code,FOOD_PRODUCT FROM DIET_CAT_FOOD_ITEMS_MST
INNER JOIN @XML.nodes('i') x(i)
ON DIET_CAT_FOOD_ITEMS_MST.CODE = x.i.value('.', 'VARCHAR(MAX)')
Ответ @RBarryYoung (выше) работал у меня. Но когда у вас есть пробелы между строковыми значениями, разделенными запятыми, тогда этот идентификатор будет опущен пробелом. Поэтому я уменьшил это значение.
Проверьте ниже фрагмент кода.
Declare @Ids varchar(50) Set @Ids = '1 , 2,3'
set @Ids=','+Replace(@Ids,' ', '')+',';
Select * from [tblEmployee]
where Charindex(','+cast(ID as varchar(8000))+',', @Ids) > 0
CREATE TABLE t
(
id INT,
col1 VARCHAR(50)
)
INSERT INTO t
VALUES (1,
'param1')
INSERT INTO t
VALUES (2,
'param2')
INSERT INTO t
VALUES (3,
'param3')
INSERT INTO t
VALUES (4,
'param4')
INSERT INTO t
VALUES (5,
'param5')
DECLARE @params VARCHAR(100)
SET @params = ',param1,param2,param3,'
SELECT *
FROM t
WHERE Charindex(',' + Cast(col1 AS VARCHAR(8000)) + ',', @params) > 0
рабочую скрипку найди здесь скрипка
Я столкнулся с той же проблемой, и я не хочу оставлять след в исходной базе данных - то есть никаких хранимых процедур или функций. Я поступил так:
declare @IDs table (Value int)
insert into @IDs values(1)
insert into @IDs values(2)
insert into @IDs values(3)
insert into @IDs values(5)
insert into @IDs values(4)
insert into @IDs values(6)
insert into @IDs values(7)
insert into @IDs values(98)
insert into @IDs values(234)
SELECT *
FROM sometable
WHERE tableid IN (select Value from @IDs)
WHERE someId IN (SELECT convert(int, value) FROM string_split(@stringOfCommaDelimitedIds, ','))
DECLARE @Ids varchar(8000);
SET @Ids = '3,5,4,6';
SELECT convert(int, value) FROM string_split(@Ids, ',')
Лучший и простой подход.
DECLARE @AccumulateKeywordCopy NVARCHAR(2000),@IDDupCopy NVARCHAR(50);
SET @AccumulateKeywordCopy ='';
SET @IDDupCopy ='';
SET @IDDup = (SELECT CONVERT(VARCHAR(MAX), <columnName>) FROM <tableName> WHERE <clause>)
SET @AccumulateKeywordCopy = ','+@AccumulateKeyword+',';
SET @IDDupCopy = ','+@IDDup +',';
SET @IDDupCheck = CHARINDEX(@IDDupCopy,@AccumulateKeywordCopy)
Простейшим способом, который я нашел, было использование FIND_IN_SET
FIND_IN_SET(имя-столбца, значения)
Значения =(1,2,3)
ВЫБЕРИТЕ имя, ГДЕ FIND_IN_SET(идентификатор, значения)
Это пригодилось для одного из моих требований, когда я не хотел использовать CTE, а также не хотел использовать внутреннее соединение.
DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';
SELECT
cn1,cn2,cn3
FROM tableName
WHERE columnName in (select Value from fn_SplitList(@ids, ','))