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

User · Answer

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

User · Answer

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

User · 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

User · Answer

This might help   select   from tbl where col like   ABC-XYZ-PQR      I ve used this in SQL Server 2005 and it worked

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt  0    BEGIN      SET  index   CHARINDEX  delimiter    text       IF   index   0  AND  LEN  text   gt  0         BEGIN           INSERT INTO  Strings VALUES   text            BREAK        END      IF   index  gt  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

User · Answer

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

[sql] How can I introduce multiple conditions in LIKE operator?

Examples related to sql

Examples related to oracle