How to handle null field value when using LIKE

Question

Table Data:

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?

what exactly are you doing here? AND ISNULL(ea.barangay_name) LIKE '%' — e4c5
– e4c5, Commented Aug 24, 2016 at 3:29
result of isnull(col) will be 1 or 0, what are you trying to get? — SIDU
– SIDU, Commented Aug 24, 2016 at 3:31

AndrewP · Accepted Answer · 2016-08-24 03:30:38Z

2

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

answered Aug 24, 2016 at 3:30

AndrewP

1,61814 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to handle null field value when using LIKE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related