where clause not selecting proper rows in a Postgres query

Question

So I have a query where I am attempting to obtain rows that contain the words citalopram or the word celexa either in the medication column or in the generic column.

First I tried:

where    
    (   medication     ilike '%citalopram%'    or 
        generic        ilike '%citalopram%'    or 
        medication     ilike '%celexa%'        or 
        generic        ilike '%celexa%')

And all was find and dandy, except, it also selected rows that contained the medication escitalopram. So I changed the query so that it looked like the following:

where    
    (   medication     ilike '%citalopram%'    or 
        generic        ilike '%citalopram%'    or 
        medication     ilike '%celexa%'        or 
        generic        ilike '%celexa%')         and 
        medication not ilike '%escitalopram%'    and 
        generic    not ilike '%escitalopram%'

Now, sure enough it eliminates the rows containing escitalopram, but now sometimes neglects rows that have:

medication=CeleXa 40mg oral tablet, generic=null
medication=citalopram 40mg oral tablet, generic=null

etc.

Not sure why this might be the problem. Any suggestions will be appreciated!

It is because of generic having a null value - worth reading this https://stackoverflow.com/questions/22818070/behaviour-of-not-like-with-null-values — Ian Kenney, Aug 31 '17 at 03:47
You might be better off doing a "whole word" search using regex, e.g. `medication ~* '\ycelexa\y'` — Nick Barnes, Aug 31 '17 at 06:23

score 0 · Answer 1 · answered Aug 31 '17 at 07:35

0

why not:

where  
concat(' ',medication,' ',generic) ilike '% citalopram%' 
  or 
concat(' ',medication,' ',generic) ilike '% celexa%'

putting space before column so if column value starts with the word it still would have a space

answered Aug 31 '17 at 07:35

Vao Tsun

47,234
13
100
132

where clause not selecting proper rows in a Postgres query

1 Answers1