在 SQL 中,我(很遗憾)经常不得不使用“LIKE
”条件,因为数据库几乎违反了所有规范化规则。我现在无法改变。但这与问题无关。
此外,我经常使用 WHERE something in (1,1,2,3,5,8,13,21)
之类的条件来提高我的 SQL 语句的可读性和灵活性。
有没有可能在不编写复杂的子选择的情况下将这两件事结合起来?
我想要像 WHERE something LIKE ('bla%', '%foo%', 'batz%')
这样简单的东西,而不是这样:
WHERE something LIKE 'bla%'
OR something LIKE '%foo%'
OR something LIKE 'batz%'
我在这里与 SQl Server 和 Oracle 合作,但我很感兴趣,如果这在任何 RDBMS 中都是可能的。
like any
/like all
:stackoverflow.com/questions/40475982/sql-like-any-vs-like-all。 (作为记录,这已在 Oracle 社区创意论坛 community.oracle.com/ideas/11592 上提出请求)
SQL 中没有 LIKE 和 IN 的组合,更不用说 TSQL (SQL Server) 或 PLSQL (Oracle)。部分原因是因为全文搜索 (FTS) 是推荐的替代方法。
Oracle 和 SQL Server FTS 实现都支持 CONTAINS 关键字,但语法仍然略有不同:
甲骨文:
WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0
SQL 服务器:
WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')
您要查询的列必须是全文索引。
参考:
使用 Oracle Text 构建全文搜索应用程序
了解 SQL Server 全文
如果您想让您的语句易于阅读,那么您可以使用 REGEXP_LIKE(从 Oracle 版本 10 开始提供)。
一个示例表:
SQL> create table mytable (something)
2 as
3 select 'blabla' from dual union all
4 select 'notbla' from dual union all
5 select 'ofooof' from dual union all
6 select 'ofofof' from dual union all
7 select 'batzzz' from dual
8 /
Table created.
原始语法:
SQL> select something
2 from mytable
3 where something like 'bla%'
4 or something like '%foo%'
5 or something like 'batz%'
6 /
SOMETH
------
blabla
ofooof
batzzz
3 rows selected.
以及使用 REGEXP_LIKE 的简单查询
SQL> select something
2 from mytable
3 where regexp_like (something,'^bla|foo|^batz')
4 /
SOMETH
------
blabla
ofooof
batzzz
3 rows selected.
但 ...
由于性能不太好,我自己不会推荐它。我会坚持使用几个 LIKE 谓词。所以这些例子只是为了好玩。
你被困在
WHERE something LIKE 'bla%'
OR something LIKE '%foo%'
OR something LIKE 'batz%'
除非您填充临时表(在数据中包含通配符)并像这样加入:
FROM YourTable y
INNER JOIN YourTempTable t On y.something LIKE t.something
试试看(使用 SQL Server 语法):
declare @x table (x varchar(10))
declare @y table (y varchar(10))
insert @x values ('abcdefg')
insert @x values ('abc')
insert @x values ('mnop')
insert @y values ('%abc%')
insert @y values ('%b%')
select distinct *
FROM @x x
WHERE x.x LIKE '%abc%'
or x.x LIKE '%b%'
select distinct x.*
FROM @x x
INNER JOIN @y y On x.x LIKE y.y
输出:
x
----------
abcdefg
abc
(2 row(s) affected)
x
----------
abc
abcdefg
(2 row(s) affected)
LIKE 'bla%'
吗?还是只能进行 LIKE '%bla%'
次搜索?
对于 PostgreSQL,有 ANY
or ALL
form:
WHERE col LIKE ANY( subselect )
或者
WHERE col LIKE ALL( subselect )
其中子选择恰好返回一列数据。
LIKE ANY
和 LIKE ALL
是所有 SQL 方言所共有的,即核心语言的一部分,还是特定于方言?
= ANY
或 <> ALL
但它仅适用于 SQL,例如,不适用于 PLSQL。
另一个解决方案应该适用于任何 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
应至少包含一列 pattern
,并且可以这样填充:
INSERT INTO table_of_patterns(pattern) VALUES ('bla%');
INSERT INTO table_of_patterns(pattern) VALUES ('%foo%');
INSERT INTO table_of_patterns(pattern) VALUES ('batz%');
如果您想封装上面显示的内部联接或临时表技术,我建议使用 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
我在这里与 SQl Server 和 Oracle 合作,但我很感兴趣,如果这在任何 RDBMS 中都是可能的。
Teradata 支持 LIKE ALL/ANY 语法:
列表中的所有字符串。 ANY 列表中的任何字符串。 ┌──────────────────────────────┬────────────────── ──────────────────┐ │ THIS 表达式…… │ 等价于这个表达式…… │ ├────────────────── ────────────┼────────────────────────────────────┤ │ 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 支持该语法:
Add synthetic [NOT] LIKE ANY and [NOT] LIKE ALL operators
很多时候,SQL 用户希望能够组合 LIKE 和 IN 谓词,例如: SELECT * FROM customer WHERE last_name [ NOT ] LIKE ANY ('A%', 'E%') [ ESCAPE '!' ] 解决方法是将谓词手动扩展为等效的 SELECT * FROM customer WHERE last_name LIKE 'A%' OR last_name LIKE 'E%' jOOQ 可以开箱即用地支持这种合成谓词。
PostgreSQL LIKE/ILIKE ANY (ARRAY[])
:
SELECT *
FROM t
WHERE c LIKE ANY (ARRAY['A%', '%B']);
SELECT *
FROM t
WHERE c LIKE ANY ('{"Do%", "%at"}');
Snowflake 还支持 LIKE ANY/LIKE ALL 匹配:
LIKE ANY/ALL 允许基于与一个或多个模式的比较对字符串进行区分大小写的匹配。
例子:
SELECT *
FROM like_example
WHERE subject LIKE ANY ('%Jo%oe%','T%e')
-- WHERE subject LIKE ALL ('%Jo%oe%','J%e')
改用内部连接:
SELECT ...
FROM SomeTable
JOIN
(SELECT 'bla%' AS Pattern
UNION ALL SELECT '%foo%'
UNION ALL SELECT 'batz%'
UNION ALL SELECT 'abc'
) AS Patterns
ON SomeTable.SomeColumn LIKE Patterns.Pattern
一种方法是将条件存储在临时表(或 SQL Server 中的表变量)中,然后像这样加入:
SELECT t.SomeField
FROM YourTable t
JOIN #TempTableWithConditions c ON t.something LIKE c.ConditionValue
我有一个简单的解决方案,至少可以在 postgresql 中使用,使用 like any
后跟正则表达式列表。这是一个示例,查看在列表中识别一些抗生素:
select *
from database.table
where lower(drug_name) like any ('{%cillin%,%cyclin%,%xacin%,%mycine%,%cephal%}')
你甚至可以试试这个
功能
CREATE FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20))
RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value varchar(8000)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END
询问
select * from my_table inner join (select value from fn_split('ABC,MOP',','))
as split_table on my_table.column_name like '%'+split_table.value+'%';
从 2016 年开始,SQL Server 包含一个 STRING_SPLIT
function。我正在使用 SQL Server v17.4,我得到了这个为我工作:
DECLARE @dashboard nvarchar(50)
SET @dashboard = 'P1%,P7%'
SELECT * from Project p
JOIN STRING_SPLIT(@dashboard, ',') AS sp ON p.ProjectNumber LIKE sp.value
我也想知道这样的事情。我刚刚使用 SUBSTRING
和 IN
的组合进行了测试,它是解决此类问题的有效方法。试试下面的查询:
Select * from TB_YOUR T1 Where SUBSTRING(T1.Something, 1,3) IN ('bla', 'foo', 'batz')
在 Oracle 中,您可以通过以下方式使用集合:
WHERE EXISTS (SELECT 1
FROM TABLE(ku$_vcnt('bla%', '%foo%', 'batz%'))
WHERE something LIKE column_value)
这里我使用了预定义的集合类型 ku$_vcnt
,但您可以像这样声明自己的集合类型:
CREATE TYPE my_collection AS TABLE OF VARCHAR2(4000);
我可能对此有一个解决方案,尽管据我所知它只能在 SQL Server 2008 中工作。我发现您可以使用 https://stackoverflow.com/a/7285095/894974 中描述的行构造函数通过 like 子句连接“虚构”表。这听起来比它更复杂,看:
SELECT [name]
,[userID]
,[name]
,[town]
,[email]
FROM usr
join (values ('hotmail'),('gmail'),('live')) as myTable(myColumn) on email like '%'+myTable.myColumn+'%'
这将导致所有用户都拥有与列表中提供的电子邮件地址类似的电子邮件地址。希望它对任何人都有用。这个问题困扰了我一段时间。
对于 Sql Server,您可以使用 Dynamic 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
可能你认为这样的组合:
SELECT *
FROM table t INNER JOIN
(
SELECT * FROM (VALUES('bla'),('foo'),('batz')) AS list(col)
) l ON t.column LIKE '%'+l.Col+'%'
如果您已经为目标表定义了全文索引,那么您可以使用以下替代方法:
SELECT *
FROM table t
WHERE CONTAINS(t.column, '"bla*" OR "foo*" OR "batz*"')
这适用于逗号分隔值
DECLARE @ARC_CHECKNUM VARCHAR(MAX)
SET @ARC_CHECKNUM = 'ABC,135,MED,ASFSDFSF,AXX'
SELECT ' AND (a.arc_checknum LIKE ''%' + REPLACE(@arc_checknum,',','%'' OR a.arc_checknum LIKE ''%') + '%'')''
评估为:
AND (a.arc_checknum LIKE '%ABC%' OR a.arc_checknum LIKE '%135%' OR a.arc_checknum LIKE '%MED%' OR a.arc_checknum LIKE '%ASFSDFSF%' OR a.arc_checknum LIKE '%AXX%')
如果您希望它使用索引,则必须省略第一个 '%'
字符。
在 Oracle RBDMS 中,您可以使用 REGEXP_LIKE 函数实现此行为。
以下代码将测试字符串 3 是否存在于列表表达式 one|two|three|four|five 中(其中管道“|”符号表示 OR 逻辑运算)。
SELECT 'Success !!!' result
FROM dual
WHERE REGEXP_LIKE('three', 'one|two|three|four|five');
RESULT
---------------------------------
Success !!!
1 row selected.
前面的表达式等价于:
three=one OR three=two OR three=three OR three=four OR three=five
所以它会成功。
另一方面,以下测试将失败。
SELECT 'Success !!!' result
FROM dual
WHERE REGEXP_LIKE('ten', 'one|two|three|four|five');
no rows selected
从 10g 版本开始,Oracle 中提供了几个与正则表达式 (REGEXP_*) 相关的函数。如果您是 Oracle 开发人员并且对此主题感兴趣,那么这应该是一个好的开始Using Regular Expressions with Oracle Database。
没有这样的答案:
SELECT * FROM table WHERE something LIKE ('bla% %foo% batz%')
在oracle中没有问题。
在 Teradata 中,您可以使用 LIKE ANY ('%ABC%','%PQR%','%XYZ%')
。下面是一个对我产生相同结果的示例
--===========
-- CHECK ONE
--===========
SELECT *
FROM Random_Table A
WHERE (Lower(A.TRAN_1_DSC) LIKE ('%american%express%centurion%bank%')
OR Lower(A.TRAN_1_DSC) LIKE ('%bofi%federal%bank%')
OR Lower(A.TRAN_1_DSC) LIKE ('%american%express%bank%fsb%'))
;
--===========
-- CHECK TWO
--===========
SELECT *
FROM Random_Table A
WHERE Lower(A.TRAN_1_DSC) LIKE ANY
('%american%express%centurion%bank%',
'%bofi%federal%bank%',
'%american%express%bank%fsb%')
很抱歉挖了一个旧帖子,但它有很多观点。本周我遇到了类似的问题,并提出了这种模式:
declare @example table ( sampletext varchar( 50 ) );
insert @example values
( 'The quick brown fox jumped over the lazy dog.' ),
( 'Ask not what your country can do for you.' ),
( 'Cupcakes are the new hotness.' );
declare @filter table ( searchtext varchar( 50 ) );
insert @filter values
( 'lazy' ),
( 'hotness' ),
( 'cupcakes' );
-- Expect to get rows 1 and 3, but no duplication from Cupcakes and Hotness
select *
from @example e
where exists ( select * from @filter f where e.sampletext like '%' + searchtext + '%' )
Exists() 比 IMO 连接好一点,因为它只测试集合中的每条记录,但如果有多个匹配项不会导致重复。
这在 Postgres 中使用 like
或 ilike
和 any
或 all
与 array
是可能的。这是一个使用 Postgres 9 对我有用的示例:
select id, name from tb_organisation where name ilike any (array['%wembley%', '%south%']);
这打印出来:
id | name -----+------------------------ 433 | South Tampa Center 613 | South Pole 365 | Bromley South 796 | Wembley Special Events 202 | Southall 111 | Wembley Inner Space
做这个
WHERE something + '%' in ('bla', 'foo', 'batz')
OR '%' + something + '%' in ('tra', 'la', 'la')
或者
WHERE something + '%' in (select col from table where ....)
不定期副业成功案例分享