Can wildcards be used in WHERE statements?

[Jim R]

if === *_*

 

Not that it's the proper syntax, but I'm wondering if I can extract values that only have an underscore in the field of data.

 

[Daniel0]

Wildcards can be used using LIKE.

 

SELECT username, email FROM users WHERE email LIKE '%@gmail.com';

The % is the wildcard here.

 

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html

[Maq]

The underscore is a single character wildcard.  If you want it literally you must escape it.

 

but I'm wondering if I can extract values that only have an underscore in the field of data.

 

That would just be an underscore by itself.  Do you mean entries with underscores anywhere in the field?  In this case:

 

'%\_%'

[Daniel0]

MySQL has a function called INSTR() actually.

 

SELECT foo FROM bar WHERE INSTR(baz, '_');

 

I don't know what's faster though.

[Jim R]

I was getting ready to post that the query was returning all the values, but escaping the underscore made it work.

 

Thanks to you both.  Is there also a way to make it omit other key words, like "main" or "page". 

[Daniel0]

Just add another statement:

... WHERE something LIKE '%\_%' AND something NOT LIKE '%main%' ...

[Jim R]

Just add another statement:

... WHERE something LIKE '%\_%' AND something NOT LIKE '%main%' ...

 

Nice.   

[Jim R]

So is this the most concise way to write this:

 

SELECT * FROM wikirecentchanges WHERE rc_title LIKE '%\_%' AND rc_title NOT LIKE '%Main%' AND rc_title NOT LIKE '%20%'

 

I tried commas.  It didn't work.

[Daniel0]

Define "didn't work".

[Jim R]

Define "didn't work".

 

I tried multiple ways, and it either ignored the statement or gave me a syntax error. 

[Maq]

Define "didn't work".

 

I tried multiple ways, and it either ignored the statement or gave me a syntax error. 

 

So, what's the error?  Make sure you have this at the end of your mysql_query() call:

 

or die(mysql_error());

[Jim R]

Ok...when I say error, I just mean within the SQLyog program I use to view and query my databases.  Sorry that I omitted that point.