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

Question

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?

This is not possible, you will need to do ...customer_comments like '%first string%' or customer_comments like '%second string%' or...And you should check if this is really necessary since like with % is slow. You should better check exact strings and just use an IN clause. — Jonas Metzler, Jun 24 '22 at 12:47

score 1 · Answer 1 · answered Jun 24 '22 at 15:18

1

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

answered Jun 24 '22 at 15:18

Mohammad

605
3
9

score 0 · Answer 2 · answered Jun 24 '22 at 12:58

0

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

Combining "LIKE" and "IN" for SQL Server

Basically you'll have to chain a bunch of 'OR' conditions.

answered Jun 24 '22 at 12:58

Jordi W. Santos

23
4

1

This has nothing to do with SQL Server, but is a general thing on each DB. Furthermore, I already wrote that in the comment above. – Jonas Metzler Jun 24 '22 at 13:03
Please provide more detail to your answer. Links are always good to include, but your answer should be helpful to the reader without them needing to click on the link. In this case you could provide an example that is specific to this question that uses the solution and explain how the solution works. – Jesse Jun 25 '22 at 16:39

Jaytiger · Answer 3 · 2022-06-24T13:59:15.103

0

Based on the post @Jordi shared, I think below query can be an option in BigQuery.

query:

SELECT DISTINCT customer_comments 
  FROM sample,
       UNNEST(['air con','no cold air','hot air','blowing hot air']) keyword
 WHERE INSTR(customer_comments, keyword) <> 0;

output:

with sample:

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

edited Jun 24 '22 at 13:59

answered Jun 24 '22 at 13:20

Jaytiger

11,626
2
5
15

score 0 · Answer 4 · answered Jun 24 '22 at 17:52

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'], '|')
)

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

4 Answers4