Advertisement
Advertisement


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.

2012/10/20
1
73
10/20/2012 12:06:21 AM

Accepted Answer

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)
2013/12/18
45
12/18/2013 6:15:52 PM


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)');
2012/11/05

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

2012/06/21

This might help:

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

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

2012/09/22

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.*'); 
2014/12/24

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)[email protected]);
             END;
         INSERT INTO @returnList
                SELECT @stringToSplit;
         RETURN;
     END;
2016/09/09