Filter a column by names that include same characters

Question

I want to filter a title column by its name (from jobs table). I want to include 'director' job positions, but not 'subdirector' job positions. How can I do it?

This is my code, but it doesn't work:

select distinct title, id  from jobs
where title like '%director%' and title not like '%sub%' 

Answer 1

Case matters, so try something like this:

    select distinct title, id  
      from jobs
     where lower(title) like '%director%' 
       and lower(title) not like '%sub%'

Your dbms may not recognize "lower", so check documentation online to see if it needs to be "lower", or "lcase", or something else.

If using postgres, you could use "ilike", which ignores case:

    select distinct title, id  
      from jobs
     where title ilike '%director%' 
       and title not ilike '%sub%'