SQL 我如何在 LIKE 运算符中引入多个条件

How can i introduce multiple conditions in LIKE operator

提问by Madhu

I want to write an SQL statement like below:

我想写一个像下面这样的SQL语句：

select * from tbl where col like ('ABC%','XYZ%','PQR%');

I know it can be done using OR. But I want to know is there any better solution.

我知道可以使用OR. 但我想知道有没有更好的解决方案。

采纳答案by Asaph

Here is an alternative way:

这是另一种方法：

select * from tbl where col like 'ABC%'
union
select * from tbl where col like 'XYZ%'
union
select * from tbl where col like 'PQR%';

Here is the test code to verify:

下面是要验证的测试代码：

create table tbl (col varchar(255));
insert into tbl (col) values ('ABCDEFG'), ('HIJKLMNO'), ('PQRSTUVW'), ('XYZ');
select * from tbl where col like 'ABC%'
union
select * from tbl where col like 'XYZ%'
union
select * from tbl where col like 'PQR%';
+----------+
| col      |
+----------+
| ABCDEFG  |
| XYZ      |
| PQRSTUVW |
+----------+
3 rows in set (0.00 sec)

回答by Bill Karwin

This is a good use of a temporary table.

这是临时表的一个很好的用途。

CREATE TEMPORARY TABLE patterns (
  pattern VARCHAR(20)
);

INSERT INTO patterns VALUES ('ABC%'), ('XYZ%'), ('PQR%');

SELECT t.* FROM tbl t JOIN patterns p ON (t.col LIKE p.pattern);

In the example patterns, there's no way colcould match more than one pattern, so you can be sure you'll see each row of tblat most once in the result. But if your patterns are such that colcould match more than one, you should use the DISTINCTquery modifier.

在示例模式中，col不可能匹配多个模式，因此您可以确保tbl在结果中每行最多只能看到一次。但是如果您的模式col可以匹配多个，您应该使用DISTINCT查询修饰符。

SELECT DISTINCT t.* FROM tbl t JOIN patterns p ON (t.col LIKE p.pattern);

回答by stefannebesnak

Oracle 10g has functions that allow the use of POSIX-compliant regular expressions in SQL:

Oracle 10g 具有允许在 SQL 中使用符合 POSIX 的正则表达式的函数：

• REGEXP_LIKE
• REGEXP_REPLACE
• REGEXP_INSTR
• REGEXP_SUBSTR

• REGEXP_LIKE
• REGEXP_REPLACE
• REGEXP_INSTR
• REGEXP_SUBSTR

See the Oracle Database SQL Referencefor syntax details on this functions.

有关此函数的语法详细信息，请参阅Oracle 数据库 SQL 参考。

Take a look at Regular expressions in Perlwith examples.

通过示例查看Perl中的正则表达式。

Code :

代码 ：

    select * from tbl where regexp_like(col, '^(ABC|XYZ|PQR)');

回答by KathMania

select * from tbl where col like 'ABC%'
or col like 'XYZ%'
or col like 'PQR%';

This works in toad and powerbuilder. Don't know about the rest

这适用于蟾蜍和 powerbuilder。不知道其余的

回答by Eric

This might help:

这可能有帮助：

select * from tbl where col like '[ABC-XYZ-PQR]%'

I've used this in SQL Server 2005 and it worked.

我在 SQL Server 2005 中使用过它并且它有效。

回答by asad

I also had the same requirement where I didn't have choice to pass like operator multiple times by either doing an OR or writing union query.

我也有同样的要求，我没有选择通过执行 OR 或编写联合查询来多次传递 like 运算符。

This worked for me in Oracle 11g:

REGEXP_LIKE (column, 'ABC.*|XYZ.*|PQR.*'); 

回答by SimarjeetSingh Panghlia

Even u can try this

即使你可以试试这个

Function

功能

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

Query

询问

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

回答by atik sarker

If your parameter value is not fixed or your value can be null based on business you can try the following approach.

如果您的参数值不固定或者您的值可以根据业务为空，您可以尝试以下方法。

DECLARE @DrugClassstring VARCHAR(MAX);
SET @DrugClassstring = 'C3,C2'; -- You can pass null also

---------------------------------------------

IF @DrugClassstring IS NULL 
    SET @DrugClassstring = 'C3,C2,C4,C5,RX,OT'; -- If null you can set your all conditional case that will return for all
SELECT dn.drugclass_FK , dn.cdrugname
FROM drugname AS dn
INNER JOIN dbo.SplitString(@DrugClassstring, ',') class ON dn.drugclass_FK = class.[Name] -- SplitString is a a function

SplitString function

拆分字符串函数

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER FUNCTION [dbo].[SplitString](@stringToSplit VARCHAR(MAX),
                                   @delimeter     CHAR(1)      = ',')
RETURNS @returnList TABLE([Name] [NVARCHAR](500))
AS
     BEGIN

         --It's use in report sql, before any change concern to everyone

         DECLARE @name NVARCHAR(255);
         DECLARE @pos INT;
         WHILE CHARINDEX(@delimeter, @stringToSplit) > 0
             BEGIN
                 SELECT @pos = CHARINDEX(@delimeter, @stringToSplit);
                 SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1);
                 INSERT INTO @returnList
                        SELECT @name;
                 SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos);
             END;
         INSERT INTO @returnList
                SELECT @stringToSplit;
         RETURN;
     END;

回答by Harry Sandal

SELECT * From tbl WHERE col LIKE '[0-9,a-z]%';

SELECT * From tbl WHERE col LIKE '[0-9,az]%';

simply use this condition of like in sql and you will get your desired answer

只需在 sql 中使用类似条件，您就会得到想要的答案