%%% value in sql query?

Question

I am familiar with this type of request:

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

But today, I come across three related percent signs in some outdated code, for example:

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

This query works on both mssql and oracle when foo has a string type (varchar, etc.), but it fails when foo is numeric.

Any idea what that means?

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

Answer 1

Turns off 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)

Answer 2

If you are trying to find any value with a percentage in it, you need to use ESCAPE:

eg.

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

foo ( ), , .

Answer 3

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

select * from tableA where foo='%'

,

ls *

ls **

, -. :   MySQL "%"   ls "*"

2%, .

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

SQL '%' '_' ( ) .
'%' , '*' Linux/Unix Windows.
'_' - '?' Linux/Unix Windows.

, "%" "%", '%%%' '%'

Answer 5

, , . ?

, foo?

wild-card LIKE. , , db , wild-card. , %%%, , - , %%%, (, =).

EDIT2: , , LIKE '%%%' , %.

Answer 6

, , , : select * from emp where ename like 'KING';

, =: select * from emp where deptno = 20;