How can I introduce multiple conditions in LIKE operator?

Question

I want to write an SQL statement like below:

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.

Answer 1

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 col could match more than one pattern, so you can be sure you'll see each row of tbl at most once in the result. But if your patterns are such that col could match more than one, you should use the DISTINCT query modifier.

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

Answer 2

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

• REGEXP_LIKE
• REGEXP_REPLACE
• REGEXP_INSTR
• REGEXP_SUBSTR

See the Oracle Database SQL Reference for syntax details on this functions.

Take a look at Regular expressions in Perl with examples.

Code :

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

Answer 3

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)

Answer 4

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

Answer 5

This might help:

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

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

Answer 6

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.

This worked for me in Oracle 11g:

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

Answer 7

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

Answer 8

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;

Answer 9

I had to add all to Asaph's answer to make it work.

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

Answer 10

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

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