MySQL Wildcard for "=" - is there one

Question

So, "SELECT * FROM table WHERE col LIKE '%'" will return everything. Is there a wildcard for the query "SELECT * FROM table WHERE col = '*'"? - clearly * doesn't work, I just put it there to indicate where I'd like a wildcard. The column I'm selecting from contains an integer between 1 and 12, and I want to be able to select either all records with a particular number, or all records with a wildcard.

Thanks,

Answer 1

The reason for using LIKE is because the = does not offer wildcard support. Otherwise there would be no reason for LIKE

Answer 2

SELECT * FROM table WHERE col RLIKE '.*'

i.e. regular-expression LIKE.

Answer 3

If you want to select everything, why are you attaching the WHERE clause at all? Just leave it off conditionally instead of putting a wildcard into it.

Answer 4

Assuming your query is parameter driven a case statement is probably appropriate

select * from mytable where col like case when @myvariable is null then % else myvariable end

Where @myvariable is either null if you dont want a value otherwise it would use the integer value you pass in.

Answer 5

LIKE is basically the same as =, except LIKE lets you use wildcards.

These two queries will return the same results:

SELECT * FROM table WHERE col LIKE 'xyz';
SELECT * FROM table WHERE col='xyz';

Without a '%' in the LIKE query, it is effectively the same as '='.

If you're doing a selection on an integer column, you should consider using the IN() or BETWEEN operators. It sounds like you have two separate conditions that should be handled in your code however, rather than in the query, as your conditions dictate that you need at least two different kinds of queries.

Edit: I should clarify that LIKE and = are similar only in normal, humdrum string comparison usage. You should check the MySQL Manual for specifics on how it works, as there are situations where it's not the same (such as language sets).

Answer 6

zombat's answer is great, but I only noticed in his answer that you are selecting integers. He mentioned IN() and BETWEEN(). Here's examples using those syntaxes, as well as some other options you have for an integer field.

SELECT * FROM table WHERE col = 1;
SELECT * FROM table WHERE col BETWEEN 1 AND 12;
SELECT * FROM table WHERE col BETWEEN 6 AND 12;
SELECT * FROM table WHERE col <= 6;
SELECT * FROM table WHERE col < 6;
SELECT * FROM table WHERE col >= 6;
SELECT * FROM table WHERE col > 6;
SELECT * FROM table WHERE col <> 6;
SELECT * FROM table WHERE col IN (1,2,5,6,10);
SELECT * FROM table WHERE col NOT IN (1,2,5,6,10);

Answer 7

Pardon my lack of schooling (more like self teaching) as a PHPMySQL programmer. Having spent over a week banging my head against a SUM query. For the most part-

$query = "SELECT * SUM(hours) FROM logger WHERE locate('%\" . $name . \"%',s_name)";

$result = mysql_query($query) or die(mysql_error());

// Print out result while($row = mysql_fetch_array($result)){ echo $row[SUM (hours)]; }

comes up clean but no SUM of hours is printed. Can someone help educate me on this please?