在SQL I中(很遗憾)经常不得不使用“LIKE”条件,因为数据库违反了几乎所有的规范化规则。我现在改变不了。但这与问题无关。
此外,我经常使用诸如WHERE(1,1,2,3,5,8,13,21)中的某些内容之类的条件,以提高SQL语句的可读性和灵活性。
有没有可能在不编写复杂的子选择的情况下将这两者结合起来?
我想要一些像WHERE一样简单的东西('bla%', '%foo%', 'batz%')而不是这样:
WHERE something LIKE 'bla%'
OR something LIKE '%foo%'
OR something LIKE 'batz%'
我在这里与SQl Server和Oracle一起工作,但我感兴趣的是,这是否可能在任何RDBMS中。
我在这里与SQl Server和Oracle一起工作,但我感兴趣的是,这是否可能在任何RDBMS中。
Teradata支持LIKE ALL/ANY语法:
ALL every string in the list.
ANY any string in the list.
┌──────────────────────────────┬────────────────────────────────────┐
│ THIS expression … │ IS equivalent to this expression … │
├──────────────────────────────┼────────────────────────────────────┤
│ x LIKE ALL ('A%','%B','%C%') │ x LIKE 'A%' │
│ │ AND x LIKE '%B' │
│ │ AND x LIKE '%C%' │
│ │ │
│ x LIKE ANY ('A%','%B','%C%') │ x LIKE 'A%' │
│ │ OR x LIKE '%B' │
│ │ OR x LIKE '%C%' │
└──────────────────────────────┴────────────────────────────────────┘
编辑:
jOOQ 3.12.0版本支持该语法:
添加合成[NOT]像任何和[NOT]像所有操作符
很多时候,SQL用户希望能够组合like和IN谓词,例如:
SELECT *
从客户
WHERE last_name [NOT] LIKE ANY ('A%', 'E%') [ESCAPE '!']
解决方法是手动将谓词展开为等效谓词
SELECT *
从客户
WHERE last_name LIKE 'A%'
或last_name LIKE 'E%'
jOOQ可以开箱即用地支持这样一个合成谓词。
LIKE/ILIKE ANY(数组[]):
SELECT *
FROM t
WHERE c LIKE ANY (ARRAY['A%', '%B']);
SELECT *
FROM t
WHERE c LIKE ANY ('{"Do%", "%at"}');
db < > fiddle演示
雪花也支持LIKE ANY/LIKE ALL匹配:
像任何/所有
允许基于与一个或多个模式比较的字符串进行区分大小写的匹配。
<subject> LIKE ANY (<pattern1> [, <pattern2>…) [ESCAPE <escape_char>]
例子:
SELECT *
FROM like_example
WHERE subject LIKE ANY ('%Jo%oe%','T%e')
-- WHERE subject LIKE ALL ('%Jo%oe%','J%e')
对于Sql Server,您可以求助于动态Sql。
大多数情况下,在这种情况下,你有基于数据库中的一些数据的in子句参数。
下面的示例有点“勉强”,但这可以匹配遗留数据库中发现的各种真实情况。
假设您有一个Persons表,其中人名存储在一个字段PersonName中,即FirstName + ' ' + LastName。
您需要从名字列表中选择所有人,这些名字存储在表NamesToSelect中的NameToSelect字段中,再加上一些附加的条件(如按性别、出生日期等过滤)
你可以这样做
-- @gender is nchar(1), @birthDate is date
declare
@sql nvarchar(MAX),
@subWhere nvarchar(MAX)
@params nvarchar(MAX)
-- prepare the where sub-clause to cover LIKE IN (...)
-- it will actually generate where clause PersonName Like 'param1%' or PersonName Like 'param2%' or ...
set @subWhere = STUFF(
(
SELECT ' OR PersonName like ''' + [NameToSelect] + '%'''
FROM [NamesToSelect] t FOR XML PATH('')
), 1, 4, '')
-- create the dynamic SQL
set @sql ='select
PersonName
,Gender
,BirstDate -- and other field here
from [Persons]
where
Gender = @gender
AND BirthDate = @birthDate
AND (' + @subWhere + ')'
set @params = ' @gender nchar(1),
@birthDate Date'
EXECUTE sp_executesql @sql, @params,
@gender,
@birthDate
另一个解决方案,应该适用于任何RDBMS:
WHERE EXISTS (SELECT 1
FROM (SELECT 'bla%' pattern FROM dual UNION ALL
SELECT '%foo%' FROM dual UNION ALL
SELECT 'batz%' FROM dual)
WHERE something LIKE pattern)
内部选择可以用另一个模式源替换,比如表(或视图),如下所示:
WHERE EXISTS (SELECT 1
FROM table_of_patterns t
WHERE something LIKE t.pattern)
Table_of_patterns应该至少包含一个列模式,可以像这样填充:
INSERT INTO table_of_patterns(pattern) VALUES ('bla%');
INSERT INTO table_of_patterns(pattern) VALUES ('%foo%');
INSERT INTO table_of_patterns(pattern) VALUES ('batz%');
如果您想封装上面所示的Inner Join或临时表技术,我建议使用TableValue用户函数。这样可以看得更清楚一点。
在使用定义在:http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx的split函数后
我们可以根据我创建的名为“Fish”的表编写以下内容(int id, varchar(50) Name)
SELECT Fish.* from Fish
JOIN dbo.Split('%ass,%e%',',') as Splits
on Name like Splits.items //items is the name of the output column from the split function.
输出
1 Bass
2 Pike
7 Angler
8 Walleye
您可以以牺牲性能为代价,为大量元素动态地执行此操作,但这是可行的。
DECLARE @val nvarchar(256),
@list nvarchar(max) = 'one,two,three,ten,five';
CREATE table #table (FIRST_NAME nvarchar(512), LAST_NAME nvarchar(512));
CREATE table #student (FIRST_NAME nvarchar(512), LAST_NAME nvarchar(512), EMAIL
nvarchar(512));
INSERT INTO #student (FIRST_NAME, LAST_NAME, EMAIL)
SELECT 'TEST', ' redOne' ,'test.redOne@toto.com' UNION ALL
SELECT 'student', ' student' ,'student@toto.com' UNION ALL
SELECT 'student', ' two' ,'student.two@toto.com' UNION ALL
SELECT 'hello', ' ONE TWO THREE' ,'student.two@toto.com'
DECLARE check_cursor CURSOR FOR select value from STRING_SPLIT(@list,',')
OPEN check_cursor
FETCH NEXT FROM check_cursor INTO @val
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @val
IF EXISTS (select * from #student where REPLACE(FIRST_NAME, ' ','')
like '%' + @val + '%' OR REPLACE(LAST_NAME, ' ','') like '%' + @val + '%')
BEGIN
INSERT INTO #table (FIRST_NAME, LAST_NAME )
SELECT TOP 1 FIRST_NAME, LAST_NAME VALUE from #student where
REPLACE(FIRST_NAME, ' ','') like '%' + @val + '%' OR REPLACE(LAST_NAME, ' ','')
like '%' + @val + '%'
END;
FETCH NEXT FROM check_cursor INTO @val
END
CLOSE check_cursor;
DEALLOCATE check_cursor;
SELECT * FROM #table;
DROP TABLE #table;
DROP TABLE #student;