meaning of %%% in sql query?

Question

I am familiar with this kind of query:

select * from tableA where foo like '%bar%'

But today I run into three adjacent percentage signs in some legacy code, like this:

select * from tableA where foo like '%%%'

This query seems to work, both on mssql and oracle, when foo is of string type (varchar, etc.) but it fails when foo is numeric.

Any idea what it means?

EDIT: sorry about the typo in original question, the query uses the LIKE operator.

Answer 1

Do you expect it to work with numeric types, esp. when you are passing in a string to compare the field with?

Also, what does the field foo has stored in it?

Usually a LIKE clause is used when doing a wild-card comparison. But, from your example it seems, the db has this field storing the wild-card criteria in it. And, %%% seems to mean - anything that contains only %%% as its value (esp. when you compare it with an = sign).

EDIT2: If I guess it right, LIKE '%%%' could mean anything that has % character in it.

Answer 2

If you're trying to find any value with a percent in it you need to use ESCAPE:

e.g.

SELECT * 
FROM SomeTable
WHERE foo LIKE '%|%%' ESCAPE '|'

If foo is numeric (in datatype), then you will get an error if you try to compare a numeric value in the column with a string.

Answer 3

select * from tableA where foo='%%%'

Is equivalent with

select * from tableA where foo='%'

It the same idea as

ls *

or

ls **

Which means that it will match anything. And in the example: for MySQL "%" matches everything and for ls "*" matches everything

If you add 2 of % it's equivalent as one.

mysql> create table foo ( bar char(20) );
Query OK, 0 rows affected (0.06 sec)

mysql> desc foo;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| bar   | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into foo values ('endwith%');
Query OK, 1 row affected (0.05 sec)

mysql> insert into foo values ('%startwith');
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values ('cont%ins');
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values ('doesnotcontain');
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo where bar like '%%%';
+----------------+
| bar            |
+----------------+
| endwith%       |
| %startwith     |
| cont%ins       |
| doesnotcontain |
+----------------+
4 rows in set (0.00 sec)

Answer 4

In SQL '%' and '_' (underscore sign) are wildcard characters.
'%' replaces any number of characters - equivalent to '*' in Linux/Unix and Windows file search.
'_' replaces one character - equivalent to '?' in Linux/Unix and Windows file search.

As other mentioned, one '%' is equivalent to any number of consecutive '%', so in your query you can replace '%%%' with '%'

Answer 5

Turns out in mysql it matches everthing:

mysql> create database foo;
Query OK, 1 row affected (0.06 sec)

mysql> use foo;
Database changed

mysql> create table foo ( bar char(20) );
Query OK, 0 rows affected (0.06 sec)

mysql> desc foo;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| bar   | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into foo values ('endwith%');
Query OK, 1 row affected (0.05 sec)

mysql> insert into foo values ('%startwith');
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values ('cont%ins');
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values ('doesnotcontain');
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo where bar like '%%%';
+----------------+
| bar            |
+----------------+
| endwith%       |
| %startwith     |
| cont%ins       |
| doesnotcontain |
+----------------+
4 rows in set (0.00 sec)