6

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 '%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%')
cihadakt
  • 3,054
  • 11
  • 37
  • 59
  • 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 `'%abc@abc.com%'` for e-mail address which is defined you should just use `='abc@abc.com'` – 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
  • 1
    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
  • @Ben yes you are right. I will check them – cihadakt Apr 19 '13 at 13:53
  • @valex I can't use equal because email address column contain values like "BLA BLA BLA " – cihadakt Apr 19 '13 at 13:55

3 Answers3

5

Try this:

Create a temp table:

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

then:

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

Select:

SELECT t.*
FROM tbl t JOIN temp p ON (t.col LIKE p.alternate);
Jesse
  • 8,605
  • 7
  • 47
  • 57
Vinayak Pahalwan
  • 2,915
  • 4
  • 26
  • 34
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('%abc@abc.com%', EIO.DigiAddress) >0 OR
      PATINDEX('%def@def.com%', EIO.DigiAddress) >0 OR
      PATINDEX('%ghi@ghi.com%', EIO.DigiAddress) >0

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

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
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'
Borniet
  • 3,544
  • 4
  • 24
  • 33