Geek Answers Handbook

How easy is it to use many LIKE conditions in SQL?

I want to use many LIKE conditions in my query. I could not find a practical solution. I tried CONTAINS, but that wont work.

Instead of this

where EIO.DigiAddress like '% abc@abc.com %' or EIO.DigiAddress like '% def@def.com %' or EIO.DigiAddress like '% ghi@ghi.com %'

I want to use something like this:

 CONTAINS(EIO.DigiAddress,'% abc@abc.com %', '% def@def.com %', '% ghi@ghi.com %')

OR

 EIO.DigiAddress IN ('% abc@abc.com %', '% def@def.com %', '% ghi@ghi.com %')
+6
source share
3 answers

Try the following:

Create a temporary table:

 CREATE TEMPORARY TABLE temp ( alternate VARCHAR(20) );

then

 INSERT INTO temp VALUES ('% abc@abc.com %'), ('% def@def.com %'), ('% ghi@ghi.com %');

Choose:

 SELECT t.* FROM tbl t JOIN temp p ON (t.col LIKE p.alternate);
+5
source

I see nothing wrong with using LIKE. But if you do not like LIKE, then use this (for MS SQLSERVER)

 where PATINDEX('% abc@abc.com %', EIO.DigiAddress) >0 OR PATINDEX('% def@def.com %', EIO.DigiAddress) >0 OR PATINDEX('% ghi@ghi.com %', EIO.DigiAddress) >0

Note. You must use the appropriate string function for PATINDEX depending on the database you are using.

+2
source

You can do this with regular expressions:

 where EIO.DigiAddress regexp '[ac|eg|gi]{3}@{1}[ac|eg|gi]{3}.com'
+1
source

More articles:


All Articles