CODEWITHSUNDEEP
sqloracle-databasenullsql-like
user2275460
user2275460

Reputation: 339

Why does LIKE '%' not match '' or NULL

Basically i have a set of fields that user can search by. so i write a query of the form:

SELECT *
  FROM my_addr_vw
 WHERE     fname LIKE :l_fname
  AND mname LIKE :l_mname
  AND lname LIKE :l_lname;

When user does not pass any value to any of the criteria, i assume a '%'.

What that does is, if a record had NULL or no value in say, mname - then the record drops off.... What is a good solution to this problem of NULL matches nothing!!

Upvotes: 2

Views: 1825

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270883

Almost any comparison to NULL produces an unknown (what I think of as a NULL result for the comparison). This includes like -- for both the pattern and the string being compared.

The where clause allows rows through only when the comparison is TRUE. So, both FALSE and unknown are filtered out.

That explains the NULL piece. The '' is a bit more abstruse because here Oracle is different from other databases and the standard. Oracle treats NULL and the empty string as the same. So, '' is really just a synonym for NULL (using default Oracle settings). So, you cannot match anything to the empty string, just like you cannot match anything to NULL.

Upvotes: 6

Bacon Bits
Bacon Bits

Reputation: 32220

What is a good solution to this problem of NULL matches nothing!!

The solution is to use IS NULL or IS NOT NULL.

SELECT *
FROM my_addr_vw
WHERE   (fname LIKE :l_fname OR fname IS NULL)
    AND (mname LIKE :l_mname OR mname IS NULL)
    AND (lname LIKE :l_lname OR lname IS NULL);

Upvotes: 1

Andomar
Andomar

Reputation: 238276

You can return a record with a null value with the is null syntax:

 where (:l_mname = '%' and mname is null) or mname like :l_mname

Upvotes: 0

Related Questions