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.

Ifill

What is wrong with OR? Or do you have a dynamic set of conditions? – Hornbook

What's wrong with ORs? The volume of typing? – Internship

Obviously, I used OR in the real time. But i want to know is there any other way to replace OR – Ifill

There are other ways than OR'ing the LIKE predicates together, but there is no better way. – Granoff

Doing UNION is just waste of time in case SELECT query is big. OR is the best way and would be enough to help the purpose of asked question. Thanks. – Incentive

What if I have a dynamic set of conditions, or my conditions is a Column from another table? – Cristincristina

Answer 1

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)

Akins

Answer 2

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);

Horny

Excellent. Exactly what I needed to remove hard-coded values from my code. My string masks are stored in a configuration table. The old code had each one hard-coded. This was not an elegant solution for changing values and would require change control protocols if I had any new values. I didn't know you could use the syntax "ON (my_field LIKE my_mask). Thanks. – Engine

Yep! You can use any expression you want as the join condition. Most people use a simple equality comparison, but that's not a limitation. – Horny

Answer 3

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

Meek

Oracle can certainly use an index for LIKE 'ABC%' but I don't believe it can use an index for REGEX operations (unless it's a funcion based index) – Kaseykasha

This is the best approach... Thanks for sharing. It was really helpful, though it was an old post. – Madagascar

This looks interesting, I will look into it, I was wondering how to edit regexp_like if its we have %ABC% , %XYZ% and %PQR% – Maimaia

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

Linderman

Actually that's the syntax the OP used; they wanted alternatives to that formulation. – Rickard

Works in Oracle SQL developer too. – Jolinejoliotcurie

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.

Schroder

Does it not give irrelevant result? – Treasure

This is perfect for pulling out partially matching codes from data sets e.g. with characters appended to the same base code to denote geographic location – Unhinge

It is not giving required records – Are

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.*'); 

Joaquinajoash

Thanks, worked for me too, what if its %ABC% or %XYZ% or %PQR% ? – Maimaia

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

Ol

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;

Crompton

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

Proffer

Answer 10

Basically it's this answer but without temporary tables (there are other possibilities if apex_string is not available)

with
foo as (
  select 'ABC ok' text from dual union all
  select 'BCD nok'     from dual union all
  select 'XYZ ok'      from dual
)
select foo.*
from foo
join apex_string.split('ABC%,XYZ%,PQR%', ',') x on foo.text like x.COLUMN_VALUE

Splendiferous

Answer 11

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

Bohemian