How to Pass list of words into SQL 'LIKE' operator-CodePudding

Iam trying to pass a list of words into SQL Like operator. The query is to return column called Customer Issue where Customer Issue matches any word in the above list.

my_list =['air con','no cold air','hot air','blowing hot air']

SELECT customer_comments

FROM table

where customer_comments like ('%air con%') #for single search

How do i pass my_list above?

CodePudding user response：

A similiar question was answered on the following, works for SQL Server:

with sample:

CREATE TEMP TABLE sample AS 
SELECT * FROM UNNEST(['air conditioner', 'cold air', 'too hot air']) customer_comments;

CodePudding user response：

Regular expression can help here. Other solution is using unnest. Which is given already.

    
SELECT customer_comments 
FROM table
where REGEXP_CONTAINS(lower(customer_comments), r'air con|no cold air|hot air|blowing hot air');

CodePudding user response：

Consider below

with temp as (
  select ['air con','no cold air','hot air','blowing hot air'] my_list
)
select customer_comments 
from your_table, (
  select string_agg(item, '|') list
  from temp t, t.my_list item
)
where regexp_contains(customer_comments, r'' || list)

There are myriad ways to refactor above based on your specific use case - for example

select customer_comments 
from your_table
where regexp_contains(customer_comments, r'' || 
  array_to_string(['air con','no cold air','hot air','blowing hot air'], '|')
)