Extract all substrings from a text column using a regular expression-CodePudding

I want to extract all substrings from a text column using a regular expression. For example, I have a text column:

cat dog house
milk cow
mouse
saw

and i have regex for 3 letters

i want to get column:

cat
dog
cow
saw

I tried substr(), but passing the column as the first argument gives an error. (P.S. To select substrings, I must use a regular expression.)

CodePudding user response：

You do not need a regular expression to do this. PostgreSQL has many split functions for strings, arrays, and other types. For example:

with mytable as (
    select 'cat dog house' as ptext 
    union all
    select 'milk cow'
    union all
    select 'mouse'
    union all
    select 'saw'
)
select unnest(string_to_array(ptext, ' ')) as pt from mytable;

-- Return: 
cat
dog
house
milk
cow
mouse
saw

CodePudding user response：

You can use something like this

select unnest(regexp_matches(sentences, '(\y\w{3}\y)', 'ig'))
from (values ('cat dog house'), ('milk cow'), ('mouse'), ('saw') ) as t (sentences)

-- output
cat
dog
cow
saw