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

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

basically, my problem is I either return ONLY the REQUIRED STRING (which is not helpful) OR REQUIRED STRING 1 of the possible strings (usually the first one) when I want the option of returning all of the options (eg, REQUIRED STRING POSSIBLE STRING_B; REQUIRED STRING POSSIBLE STRING_X, etc.)

Hope this makes sense!

CodePudding user response：

"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')

CodePudding user response：

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 ||  '%' 
                      ) 
           );
$$;