Like '%' in where where: is always ignored

Question

To simplify / generalize my SQL, I want to use this code:

SELECT ... FROM ... WHERE ColumnName LIKE '[To_be_replaced]'

If some field of my gui is empty, I want to replace "To_be_replaced" with "%":

SELECT ... FROM ... WHERE ColumnName LIKE '%'

but if it contains something, [To_be_replaced] "is replaced by the contents, for example:

SELECT ... FROM ... WHERE ColumnName LIKE 'foo'

My question is: do all (or core) SQL Engines optimize this code by simply ignoring when the condition is when the content like "%" is one?

Answer 1

Oracle does NOT ignore it. Indeed, Oracle does not comply with the SQL standard; for Oracle, the length of the zero length is the same as NULL, and comparisons with NULL are never TRUE.

Oracle ( Oracle), , "" ) where str_column LIKE '%' , where str_column IS NOT NULL. , EXPLAIN, LIKE . EXPLAIN : is not null like '%', .

, Oracle, LIKE , (- Oracle ).

: ! NULL TRUE, ...like '%' is not null. - (. ). , .

, Oracle EXPLAIN PLAN is not null, STILL like '%'. .

: , not null. like '%' PLUS EXPLAIN, is not null ( ).