SQL 如何使用通配符 (LIKE) 和数组 (IN) 进行选择？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8520469/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to SELECT using both wildcards (LIKE) and array (IN)?
提问by Dave
In SQL, if you want to perform a SELECT with a wildcard, you'd use:
在 SQL 中，如果您想使用通配符执行 SELECT，您可以使用：
SELECT * FROM table_name WHERE field_name LIKE '%value%'
If you wanted to use an array of possible values, you'd use:
如果您想使用一组可能的值，您可以使用：
SELECT * FROM table_name WHERE field_name IN ('one', 'two', 'three')
But, what would you do if you wanted to use both wildcards AND an array?
但是，如果您想同时使用通配符和数组，您会怎么做？
Kind of like:
就像：
SELECT * FROM table_name WHERE field_name LIKE IN ('%one', '_two', 'three[abv]')
采纳答案by LaGrandMere
SELECT *
FROM table_name
WHERE field_name LIKE '%one'
OR field_name LIKE '_two'
OR field_name LIKE 'three[abv]'
回答by ali youhannaei
you can use join with like statement like below query:
您可以将 join 与 like 语句一起使用，如下面的查询：
SELECT * FROM table_name t
JOIN dbo.Split('one,two,three',',') s ON t.field_name LIKE N'%'+s.item+'%'
I create this function to split string:
我创建了这个函数来分割字符串：
CREATE FUNCTION [dbo].[Split] (@StringToSplit NVARCHAR(MAX), @SpliterChar CHAR(1))
RETURNS
@returnList TABLE ([item] [NVARCHAR] (500))
AS
BEGIN
DECLARE @nItem NVARCHAR(500);
DECLARE @pos INT;
WHILE CHARINDEX(@SpliterChar, @StringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(@SpliterChar, @StringToSplit);
SELECT @nItem = SUBSTRING(@StringToSplit, 1, @pos-1);
if (@nItem <> '') INSERT INTO @returnList SELECT @nItem;
SELECT @StringToSplit = SUBSTRING(@StringToSplit, @pos+1, LEN(@StringToSplit)-@pos);
END
if (@StringToSplit<>'') INSERT INTO @returnList SELECT @StringToSplit;
RETURN
END
