Can I make WHERE col LIKE '%' select NULL values too?

Question

I am trying to make a selection from a MySQL database using the syntax WHERE col LIKE '%' but I want to select even NULL values with that query. Is there some command/operator that can select any data, including null?

Answer 1

SELECT * FROM table WHERE (job LIKE '%' OR job IS NULL)

Answer 2

Use the IFNULL operator

WHERE IFNULL(xxx, '') LIKE '%'

Answer 3

Use COALESCE instead of IFNULL if you want to use it with java createQuery

WHERE COALESCE (xxx, '') LIKE '%'

Answer 4

If you want column1 LIKE '%' and want column1 IS NULL, why not just drop the WHERE clause?

Try:

SELECT * FROM TABLE;

Here's what I tried:

mysql> create table foo (a char(30));
Query OK, 0 rows affected (0.02 sec)

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

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

mysql> select * from foo;
+------+
| a    |
+------+
|      |
| NULL |
+------+
2 rows in set (0.00 sec)

mysql> select * from foo where a like '%' or a is null;
+------+
| a    |
+------+
|      |
| NULL |
+------+
2 rows in set (0.00 sec)

mysql> select * from foo where ifnull (a, 1) like '%';
+------+
| a    |
+------+
|      |
| NULL |
+------+
2 rows in set (0.00 sec)

mysql> 

After all, IFNULL(column1, 1) LIKE '%' is effectively a NO-OP ...