Question 90773 in PostgreSQL Question posted in
The official documentation can be found here.

SQL query to find column value contains all vowels – Postgresql

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

Tags:

2

Answers


  1. 0

    You can use ilike all() to make that shorter

    select str 
from letters 
where str ilike all (array['%a%', '%e%' , '%i%', '%o%', '%u%'])
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search