Ethyl Casin Ethyl Casin - 3 months ago 9
MySQL Question

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?

Answer

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%'
Comments