SQL query to find column value contains all vowels-CodePudding

How to filter the row which contains all vowels in the column value. for example, the table letters contains list of column value.

str
----
apple
orange
education

I tried the sql with like command.

select str from letters 
 where 
    str like '%a%'
    and str like '%e%'
     and str like '%i%'
      and str like '%o%'
       and str like '%u%'

Would like to know is there any better way to handle this? Expected output is : education

CodePudding user response：

You can use ilike all() to make that shorter

select str 
from letters 
where str ilike all (array['%a%', '%e%' , '%i%', '%o%', '%u%'])

CodePudding user response：

You can use arrays for that

the build in array function @> means contains, which does exactly what you want, so all values from the ARRAY['a','e','i','o','u'] must be in the array regexp_split_to_array(str, '\s*')

manual array functions

a_horse_with_no_name write in the comments

regexp_split_to_array(str, '\s*') can be simplified to string_to_array(str, null)

so i added it to the answer

SELECT * FROm table1
  WHERE 
  regexp_split_to_array(str, '\s*') @> ARRAY['a','e','i','o','u']

str
education

SELECT 1

SELECT * FROm table1
  WHERE
string_to_array(str, null) @> ARRAY['a','e','i','o','u']

str
education

SELECT 1

fiddle