How can I introduce several conditions in a LIKE statement

Question

I want to write an SQL statement as shown below:

select * from tbl where col like ('ABC%','XYZ%','PQR%'); 

I know that this can be done using OR . But I want to know if there is a better solution.

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) 

Answer 2

It is useful to use 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 template examples, there is no way that col can match more than one template, so you can be sure that tbl won't see each tbl line anymore as a result. But if your templates are such that col can match more than one, you should use the DISTINCT request modifier.

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

Answer 3

Oracle 10g has features that allow you to use POSIX-compatible regular expressions in SQL:

• REGEXP_LIKE
• REGEXP_REPLACE
• REGEXP_INSTR
• REGEXP_SUBSTR

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

Take a look at regular expressions in Perl with examples.

The code:

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

Answer 4

This can help:

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

I used this in SQL Server 2005 and it worked.

Answer 5

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

It works in the toad and powerbuilder. I don't know about the rest

Answer 6

I also had the same requirement when I had no choice to go through as an operator several times by executing an OR or write union request.

 This worked for me in Oracle 11g: REGEXP_LIKE (column, 'ABC.*|XYZ.*|PQR.*'); 

Answer 7

Even you can try it

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 

Inquiry

 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 may 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 aa 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 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

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

just use this condition as in sql and you will get the desired answer