Bigquery: how to select multiple matching records from a column

Question

I need to write a query to fetch the matching records from a column. The table 'abc' has this column 'category'.

mango
mango,orange
mango,orange,banana
pineapple
guava
grapes
guava,blueberry
.
.
.

The desired result is this:

mango
mango,orange
mango,orange,banana
guava
guava,blueberry
.
.
.
.

My code is like this:

Select * 
From abc
Where column_name like '%mango%' 
      and column name like '%guava%'

Category column has thousands of rows and I can not hard-code each of them. Is there any way I can get the desired output? Thanks in advance!

user_5221944 · Answer

Consider below options

select * 
from your_table 
where exists   (
    select * from unnest(split(fruits)) fruit 
    where fruit in ('mango', 'guava')
  )

or

select distinct t.* 
from your_table t,  unnest(split(fruits)) fruit 
where fruit in ('mango', 'guava')

if applied to sample data in your question - output is