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

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

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

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

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

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

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

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

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

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

Examples related to sql

Examples related to oracle