CODEWITHSUNDEEP
sqlpostgresql
Jeremy C
Jeremy C

Reputation: 25

Trying to write a WHERE LIKE statement with AND *and* OR

I seem to not be able to find this question, just iterations of it. So here we go:

I'm trying to write this (note the +* is for explanation)

SELECT column FROM table
WHERE column LIKE '[REQUIRED STRING]' [??] 'POSSIBLE STRING_A' OR 'POSSIBLE STRING_B'...etc

Hope this makes sense!

Upvotes: 0

Views: 58

Answers (2)

Belayer
Belayer

Reputation: 14932

You are rather lacking in details, seems you forgot the +* explanation, and missing all sample data and table description. So I make a couple assumptions. You are looking for a specific sub-string and at least 1 of the specified sub-strings all within a single column. Second, since you are trying to emulate LIKE the specified strings must appear as specified but can have leading and/or trailing components, similar to %sub-string%. So that is exactly what it does. The technique will be to create an array out of the optional strings when UNNEST it, also take care of the case of there not being any optional values. So: (see demo)

create or replace 
function like_with_and_or(
         required_string text 
       , optional_strigs text[] 
       )
  returns setof mtable
  language sql 
as $$
   select * 
     from mtable mt  
     where mt.col1 like '%' || required_string ||  '%'
       and (   array_ndims(optional_strigs) is null     -- no optional_strigs
            or exists ( select null
                          from unnest(optional_strigs) os
                         where mt.col1 like '%' || os ||  '%' 
                      ) 
           );
$$;

Upvotes: 0

jjanes
jjanes

Reputation: 44403

"column" and LIKE must be repeated each time. They will not be just understood to be there, like might happen in human speech.

"column" LIKE 'required' AND ("column" LIKE 'option1' OR "column" LIKE 'option2')

Upvotes: 1

Related Questions