没有LIKE&的组合;在SQL中,更不用说TSQL(sqlserver)或PLSQL(Oracle)。部分原因是因为全文搜索(FTS)是推荐的替代方法。

神谕:

WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0

SQL服务器:

WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')

要查询的列必须为全文索引。

参考文献:

• Building Full-Text Search Applications with Oracle Text
• Understanding SQL Server Full-Text

如果您想使您的语句易于阅读,那么可以使用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.

SQL> select something
  2    from mytable
  3   where regexp_like (something,'^bla|foo|^batz')
  4  /

SOMETH
------
blabla
ofooof
batzzz

3 rows selected.

但是。。。

由于表现不太好,我自己不推荐。我会坚持使用几个类似的谓词。所以这些例子只是为了好玩。

你被困住了

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)

对于PostgreSQL ANY or ALL form:

WHERE col LIKE ANY( subselect )

或

WHERE col LIKE ALL( subselect )

其中subselect只返回一列数据。

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)

内部select可以用另一个模式源(如表(或视图)替换,方法如下:

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

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

改用内部联接:

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

SELECT t.SomeField
FROM YourTable t
   JOIN #TempTableWithConditions c ON t.something LIKE c.ConditionValue

Teradata支持 LIKE ALL/ANY 语法:

全部 列表中的所有字符串。
任何 列表中的任何字符串。

┌──────────────────────────────┬────────────────────────────────────┐
│      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支持以下语法:

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可以支持这种现成的合成谓词。

LIKE/ILIKE ANY (ARRAY[]) :

SELECT *
FROM t
WHERE c LIKE ANY (ARRAY['A%', '%B']);

SELECT *
FROM t
WHERE c LIKE ANY ('{"Do%", "%at"}');

db<>fiddle demo

雪花也支持 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')

你甚至可以试试这个

功能

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+'%';

我有一个简单的解决方案,在 postgresql语言 至少,使用 like any 然后是正则表达式列表。下面是一个例子,看看在列表中识别一些抗生素:

select *
from database.table
where lower(drug_name) like any ('{%cillin%,%cyclin%,%xacin%,%mycine%,%cephal%}')

从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')

甲骨文公司

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,您可以使用动态Sql。

下面的示例有点“强制”,但这可以匹配遗留数据库中的各种实际案例。

珀森斯 其中人名存储在单个字段中 人名 名字+“”+姓氏。 姓名选择 在表中 名称选择 ,再加上一些附加标准(如按性别、出生日期等筛选)

-- @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

我可能有一个解决方案,虽然它将只在SQLServer2008工作,据我所知。我发现可以使用中描述的行构造函数 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+'%' 

这将导致所有用户的电子邮件地址与列表中提供的相同。 希望对任何人都有用。这个问题困扰了我一段时间。

你可能会这样想:

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*"')

如果您使用的是MySQL,那么最接近的就是全文搜索:

Full-Text Search, MySQL Documentation

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%')

如果希望它使用索引,必须省略第一个 '%' 性格。

在oraclerbdms中,可以使用 REGEXP_LIKE

下面的代码将测试字符串 存在于列表表达式中 一 二 | | 五 (其中管道 “符号意味着逻辑运算)。

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\ ux*)相关的函数。如果您是Oracle开发人员,并且对本主题感兴趣,那么这应该是一个很好的开始 Using Regular Expressions with Oracle Database .

没有这样的回答:

SELECT * FROM table WHERE something LIKE ('bla% %foo% batz%')

在甲骨文里没问题。

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%')

这就是我目前提出的将带分隔符的字符串(例如进入存储过程的参数)转换为SQL块的方法。我叫它“苔藓”是指“像在里面”。了解了?

地衣.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================
-- Lichen - Scalar Valued Function
-- Returns nvarchar(512) of "LIKE IN" results.  See further documentation.
-- CREATOR: Norman David Cooke
-- CREATED: 2020-02-05
-- UPDATED:
-- =======================================================================
CREATE OR ALTER FUNCTION Lichen 
(
    -- Add the parameters for the function here
    @leadingAnd bit = 1,
    @delimiter nchar(1) = ';',
    @colIdentifier nvarchar(64),
    @argString nvarchar(256)
)
RETURNS nvarchar(512)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @result nvarchar(512)

    -- set delimiter to detect (add more here to detect a delimiter if one isn't provided)
    DECLARE @delimit nchar(1) = ';'
    IF NOT @delimiter = @delimit 
        SET @delimit = @delimiter


    -- check to see if we have any delimiters in the input pattern
    IF CHARINDEX(@delimit, @argString) > 1  -- check for the like in delimiter
    BEGIN  -- begin 'like in' branch having found a delimiter
        -- set up a table variable and string_split the provided pattern into it.
        DECLARE @lichenTable TABLE ([id] [int] IDENTITY(1,1) NOT NULL, line NVARCHAR(32))
        INSERT INTO @lichenTable SELECT * FROM STRING_SPLIT(@argString, ';')

        -- setup loop iterators and determine how many rows were inserted into lichen table
        DECLARE @loopCount int = 1
        DECLARE @lineCount int 
        SELECT @lineCount = COUNT(*) from @lichenTable

        -- select the temp table (to see whats inside for debug)
        --select * from @lichenTable

        -- BEGIN AND wrapper block for 'LIKE IN' if bit is set
        IF @leadingAnd = 1
            SET @result = ' AND ('
        ELSE
            SET @result = ' ('

        -- loop through temp table to build multiple "LIKE 'x' OR" blocks inside the outer AND wrapper block
        WHILE ((@loopCount IS NOT NULL) AND (@loopCount <= @lineCount))
        BEGIN -- begin loop through @lichenTable
            IF (@loopcount = 1) -- the first loop does not get the OR in front
                SELECT @result = CONCAT(@result, ' ', @colIdentifier, ' LIKE ''', line, '''') FROM @lichenTable WHERE id = @loopCount
            ELSE  -- but all subsequent loops do
                SELECT @result = CONCAT(@result, ' OR ', @colIdentifier, ' LIKE ''', line, '''') FROM @lichenTable WHERE id = @loopCount
            SET @loopcount = @loopCount + 1     -- increment loop
        END -- end loop through @lichenTable

        -- set final parens after lichenTable loop
        SET @result = CONCAT(@result, ' )')
    END  -- end 'like in' branch having found a delimiter
    ELSE -- no delimiter was provided
    BEGIN   -- begin "no delimiter found" branch
        IF @leadingAnd = 1 
            SET @result = CONCAT(' AND ', @colIdentifier, ' LIKE ''' + @argString + '''')
        ELSE
            SET @result = CONCAT(' ', @colIdentifier, ' LIKE ''' + @argString + '''')
    END     -- end "no delimiter found" branch

    -- Return the result of the function
    RETURN @result
END  -- end lichen function

GO

分隔符检测可能是计划好的,但目前它默认为分号,因此您可以将 default 在那里。这里面可能有虫子。这个 @leadingAnd

用法示例(argString中带分隔符)

SELECT [dbo].[Lichen] (
   default        -- @leadingAND, bit, default: 1
  ,default        -- @delimiter, nchar(1), default: ';'
  ,'foo.bar'      -- @colIdentifier, nvarchar(64), this is the column identifier
  ,'01%;02%;%03%' -- @argString, nvarchar(256), this is the input string to parse "LIKE IN" from
)
GO

 AND ( foo.bar LIKE '01%' OR foo.bar LIKE '02%' OR foo.bar LIKE '%03%' ) 

用法示例(argString中不带分隔符)

SELECT [dbo].[Lichen] (
   default        -- @leadingAND, bit, default: 1
  ,default        -- @delimiter, nchar(1), default: ';'
  ,'foo.bar'      -- @colIdentifier, nvarchar(64), this is the column identifier
  ,'01%'          -- @argString, nvarchar(256), this is the input string to parse "LIKE IN" from
)
GO

 AND foo.bar LIKE '01%'

我将继续这方面的工作,所以如果我忽略了一些东西(显而易见或其他),请随时发表评论或联系。

抱歉,我翻了一篇旧文章,但它有很多观点。本周我遇到了一个类似的问题,我想到了这个模式:

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 + '%' )

WHERE something + '%' in ('bla', 'foo', 'batz')
OR '%' + something + '%' in ('tra', 'la', 'la')

或

WHERE something + '%' in (select col from table where ....)