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,

score 15 · Accepted Answer · answered Apr 29 '09 at 21:43

15

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).

answered Apr 29 '09 at 21:43

zombat

92,731
24
156
164

Thanks, just what I was looking for. Will handle it in code and change the query to include that particular WHERE clause if and when it's needed. – Dan Apr 29 '09 at 21:46
Does MySQL support BETWEEN? I thought that was just TSQL – Joe Phillips Apr 29 '09 at 22:48
@Joe: BETWEEN is standard SQL. I've never met any system supporting SQL that doesn't support BETWEEN. – Larry Lustig Oct 28 '10 at 02:49

score 8 · Answer 2 · answered Apr 29 '09 at 21:40

8

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.

answered Apr 29 '09 at 21:40

Chad Birch

73,098
23
151
149

1

I was thinking that, then I thought, no, that's nonsensical, he must just be using the wildcard as an example and really he would anchor text on one or both sides of it. Then I reread the question in detail and no, you're right, he just needs to leave off the WHERE for his application. – chaos Apr 29 '09 at 21:42
1

Dan, you know that the WHERE part is optional, right? If you leave it off, then you'll select every row. – allyourcode Apr 29 '09 at 21:46
On the contrary, it's easier to have an optional argument if you do it this way. You can prepare a single statement and, if there are no constraints required, simply pass `%` to `execute()` in the case of PDO. If there are constraints, it works just the same as an =. This made my code so much cleaner because as of php7, I can do this `$stmt->execute([$args['class'] ?? '%']);` instead of preparing two separate big 'ole queries! – Kenneth Jan 22 '19 at 19:30

Joe Phillips · Answer 3 · 2010-10-28T03:11:10.703

7

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

edited Oct 28 '10 at 03:11

answered Apr 29 '09 at 21:39

Joe Phillips

49,743
32
103
159

score 1 · Answer 4 · answered Apr 29 '09 at 21:40

1

SELECT * FROM table WHERE col RLIKE '.*'

i.e. regular-expression LIKE.

answered Apr 29 '09 at 21:40

chaos

122,029
33
303
309

Thanks for reminding me of that nice MySQL operator. RLIKE aka REGEXP is worth knowing: http://dev.mysql.com/doc/refman/5.1/en/regexp.html – artlung Apr 29 '09 at 22:34

score 0 · Answer 5 · edited May 12 '15 at 09:58

0

I have encountered such a case while building a stored procedure for a report Following is my solution, hope this is what you had in mind :)

set @p = "ALL";

Query:

select * from fact_orders
where
dim_country_id = if(@p is null or @p="ALL", dim_country_id, @p)
limit 10
;

edited May 12 '15 at 09:58

Konrad Krakowiak

12,285
11
58
45

answered May 12 '15 at 09:50

Izik

1

score 0 · Answer 6 · answered Oct 26 '15 at 22:46

If your values are in the the range (1,12) then:

select * from table where col>=5 and col<=5; //this is equal to col=5

select * from table where col>=0 and col<=12; //this is equal to col=any value

The same line can produce both effects by choosing the 2 parameters appropriately. I faced a similar problem when I needed a single prepared statement which should work with 2 different ways , either checking for a particular value in a column or ignoring that column completely.

score 0 · Answer 7 · edited Apr 01 '12 at 21:29

0

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.

edited Apr 01 '12 at 21:29

Paul Bellora

54,340
18
130
181

answered Apr 29 '09 at 21:42

u07ch

13,324
5
42
48

score 0 · Answer 8 · answered Apr 29 '09 at 22:43

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);

MySQL Wildcard for "=" - is there one

8 Answers8

Linked

Related