MYsql select/where statements using in and and like

[snorky]

mysql select ... where ...  in or like

 

Consider these statements:

Code:

 

select * from[i] sometable[/i] where[i] thiscolumn[/i] in(1,2,3);

 

That returns all rows where column thiscolumn is one of those three values.

 

Code:

 

select * from [i]sometabl[/i]e where [i]thiscolumn[/i] like("a%");

 

That returns all rows where the first character of a column thiscolumn is 'a';

 

What I'm trying to do is to combine the two concepts as in something like

 

select * from [i]sometable[/i] where  [i]thiscolumn[/i] in('like("b%")','like("c%")');

 

I'm trying to return all rows where the first character of a column thiscolumn is 'b' or 'c';

 

That syntax is wrong.

 

I've tried assigning variables

set @var1: 'like="b%"';
set @var2: 'like="c%"';

then using those vars in the in predicate.

select * from [i]sometable[/i] where  [i]thiscolumn[/i] in('b','c');

Again, bad syntax.

 

I could use ...

Code:

thiscolumn like("b%") or thiscolumn like("c%") or thiscolumn like("da%") or ....

 

... ad nausem. But if there are many values in the set, it becomes quite clunky.

 

I've tried the mysql manual and google. The problem with those is trying to figure out how to ask the question. A forum like this gives me the ability to (hopefully)seek, explain, and find the solution.

 

In this case I don't have the luxury of using php; I have to do it within mysql.

 

Ideas?

__________________

Certified Digital Forensics Examiner, MCSE, etc., etc.

Nature: Nerd. Nurture:Linux

[mikosiko]

mixing IN and LIKE  .... not possible as far as I'm aware of ... syntax for both clause are very clear.

 

seems to me that you are looking to do something for what REGEXP is more suitable... look the examples and sure you will find the answer

 

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

 

something like

 

WHERE thiscolumn REGEXP "expr1 | expr2 | expr3... etc"      where "expr?" is a formal regular expresion.

 

hope this help

[mikosiko]

you also can search for examples here

http://www.phpfreaks.com/forums/index.php/board,43.0.html

[snorky]

you also can search for examples here

http://www.phpfreaks.com/forums/index.php/board,43.0.html

 

(whacks self upside the head) Regex ! Of course!

I spent hours going down the wrong path, running down a ditch.

Thank you.