how to use many LIKE conditions in SQL easily?

Question

I want to use many LIKE conditions in my query. I couldn't find a practical solution. I tried CONTAINS but it doesn't work.

Instead of using this

where EIO.DigiAddress like '%[email protected]%'
or EIO.DigiAddress like '%[email protected]%'
or EIO.DigiAddress like '%[email protected]%'

I want to use something like this:

CONTAINS(EIO.DigiAddress,'%[email protected]%', '%[email protected]%', '%[email protected]%')

OR

EIO.DigiAddress IN ('%[email protected]%', '%[email protected]%', '%[email protected]%')

Is it a specific set of strings like that, or a common format, like an email address? — LeonardChallis, Apr 19 '13 at 11:48
Why do you use LIKE `'%[email protected]%'` for e-mail address which is defined you should just use `='[email protected]'` — valex, Apr 19 '13 at 11:49
If your intention is to check for valid email addresses. Then the last % is not needed! — IndoKnight, Apr 19 '13 at 11:56
This looks like an [XY problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). Why do you need many LIKE onditions? Usually it's best to avoid them as they are pretty costly. So -- what is the actual problem you're trying to solve? — creinig, Apr 19 '13 at 11:59
Also a duplicate of http://stackoverflow.com/questions/610796/is-there-any-way-to-combine-in-with-like-in-an-sql-statement, http://stackoverflow.com/questions/2130907/how-to-combine-in-operator-with-like-condition-or-best-way-to-get-comparable-re, http://stackoverflow.com/questions/1019745/combining-like-with-in-in-sql, http://stackoverflow.com/questions/8408900/sql-server-combining-like-and-in, http://stackoverflow.com/questions/3014940/is-there-a-combination-of-like-and-in-in-sql etc. — Ben, Apr 19 '13 at 11:59
When I look at all the answers below, I think that multiple LIKEs are still the best and cleanest solution. Sorry, but that's how you do it in SQL. — Adam Dyga, Apr 19 '13 at 12:03
@valex I can't use equal because email address column contain values like "BLA BLA BLA " — cihadakt, Apr 19 '13 at 13:55

score 5 · Answer 1 · edited Apr 19 '13 at 12:03

5

Try this:

Create a temp table:

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

then:

INSERT INTO temp
VALUES ('%[email protected]%'), ('%[email protected]%'), ('%[email protected]%');

Select:

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

edited Apr 19 '13 at 12:03

Jesse

8,605
7
47
57

answered Apr 19 '13 at 11:55

Vinayak Pahalwan

2,915
4
26
34

Does creating a temp table slows down the query? – cihadakt Apr 19 '13 at 13:58
@cihata87 i guess no..... – Vinayak Pahalwan Apr 25 '13 at 08:42

IndoKnight · Answer 2 · 2013-04-19T11:54:26.877

2

I don't see anything wrong in using LIKE. But if you don't like LIKE then use this (for MS SQLSERVER)

where PATINDEX('%[email protected]%', EIO.DigiAddress) >0 OR
      PATINDEX('%[email protected]%', EIO.DigiAddress) >0 OR
      PATINDEX('%[email protected]%', EIO.DigiAddress) >0

Note: You have to use relevant string function for PATINDEX depending on the database you use.

edited Apr 19 '13 at 11:54

answered Apr 19 '13 at 11:49

IndoKnight

1,846
1
21
29

Better yet CHARINDEX because it is faster in his case. – usr Apr 19 '13 at 12:17
PATINDEX can use wildcard characters, but CHARINDEX cannot. Please refer http://msdn.microsoft.com/en-us/library/ms190184(v=sql.90).aspx – IndoKnight Apr 19 '13 at 12:18
He's only using wildcards at the ends. It looks like his intention is a "contains" style search. – usr Apr 19 '13 at 12:22

score 1 · Answer 3 · answered Apr 19 '13 at 11:56

1

You can do this with regular expressions:

where EIO.DigiAddress regexp '[a-c|e-g|g-i]{3}@{1}[a-c|e-g|g-i]{3}.com'

answered Apr 19 '13 at 11:56

Borniet

3,544
4
24
33

how to use many LIKE conditions in SQL easily?

3 Answers3