SQL: Regex String to Table

You are given a table random\_string that has the following format:

random_string schema

• text

The text field holds a single row which contains a random string.

Your task is to take the random string and split it on each vowel (a, e, i, o, u) then the resultant substrings will be contained in the output table, formatted as:

output table schema

• results

Note that the vowels should be removed.

If there are no vowels, there will only be one row returned. Where there are multiple vowels in succession, you will see empty rows. A row should be created on each break, whether there is content in the row or not.

Regex is advised but not mandatory.

 

Solution:

SELECT 
  regexp_split_to_table(text, 'a|e|i|o|u') AS results 
FROM 
  random_string

 

regexp_split_to_table(string text, pattern text [, flags text])

 

Split string using a POSIX regular expression as the delimiter. 

See Section 9.7.3 for more information.

 

regexp_split_to_table('hello world', '\s+')

hello
world
(2 rows)

 

 

Result:

results

cpf

v

qjlzgs

bt

 

 

Reference:

String Functions and Operators