'HQL: How to check if all vals from regexp_split_to_array are in a list

The following works for single values

if pets is DOG

where pets in ('DOG', 'CAT', 'DRAGON')

But, if I have a comma separated list of pets - {DOG, CAT} generated from regexp_split_to_array it does not

This is the idea of what I want to do, but how can it actually be done?

regexp_split_to_array(pets, ',') in ('DOG', 'CAT', 'DRAGON')

arraysregexpostgresqlsplitcontains


Solution 1:[1]

select regexp_split_to_array(pets, '\s*,\s*') pets
from (
  select 'dog, cat' as pets
  union
  select 'cat, mouse'
  union 
  select 'dragon'
) t
where regexp_split_to_array(pets, '\s*,\s*') && array['dog', 'cat'];

    pets
-------------
 {dog,cat}
 {cat,mouse}
(2 rows)

= = =

select regexp_split_to_array(pets, '\s*,\s*') pets
from (
  select 'dog, cat' as pets 
  union 
  select 'cat, mouse' 
  union 
  select 'dragon'
) t 
where regexp_split_to_array(pets, '\s*,\s*') && array['dog'];

   pets
-----------
 {dog,cat}
(1 row)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 arhak

Related Questions