CODEWITHSUNDEEP
sqlgoogle-bigquery
Jayashree Sridhar
Jayashree Sridhar

Reputation: 65

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

I am 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?

Upvotes: 0

Views: 3982

Answers (5)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Use LIKE ANY

SELECT customer_comments
FROM table
WHERE customer_comments LIKE ANY ('%air con%', '%no cold air%', '%hot air%', '%blowing hot air%')

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

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

Upvotes: 0

Mohammad
Mohammad

Reputation: 678

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

Upvotes: 1

Jaytiger
Jaytiger

Reputation: 12234

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:

enter image description here

with sample:

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

Upvotes: 0

Jordi W. Santos
Jordi W. Santos

Reputation: 23

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.

Upvotes: 0

Related Questions