CODEWITHSUNDEEP
mysqlsqldatabase
Ethyl Casin
Ethyl Casin

Reputation: 793

How to handle null field value when using LIKE

Table Data:

enter image description here

I have this sql script:

SELECT
    ei.objid
FROM entityindividual ei 
INNER JOIN entity e 
    ON ei.objid = e.objid
LEFT JOIN entity_address ea 
    ON ei.objid = ea.parentid
WHERE ei.gender = 'M'
AND ISNULL(ea.barangay_name) LIKE '%'

If you run this script, 6 records will be displayed, but without using ISNULL(ea.barangay_name), only 1 record will be diplayed.

But consider this scenario:

SELECT
    ei.objid
FROM entityindividual ei 
INNER JOIN entity e 
    ON ei.objid = e.objid
LEFT JOIN entity_address ea 
    ON ei.objid = ea.parentid
WHERE ei.gender = 'M'
AND ISNULL(ea.barangay_name) LIKE '%BUENAVISTA%'

The PROBLEM is no records will display when you run the script above. WHY? How to fix this one?

Upvotes: 0

Views: 32

Answers (1)

AndrewP
AndrewP

Reputation: 1618

Try using coalesce instead of isnull.

http://www.w3resource.com/mysql/comparision-functions-and-operators/coalesce-function.php

SELECT
    ei.objid
FROM entityindividual ei 
INNER JOIN entity e 
    ON ei.objid = e.objid
LEFT JOIN entity_address ea 
    ON ei.objid = ea.parentid
WHERE ei.gender = 'M'
AND coalesce(ea.barangay_name,'') LIKE '%BUENAVISTA%'

Upvotes: 2

Related Questions