Extracting multiple substrings from a single string using regular expressions in Snowflake

user_17621822 0
Viewed 115

Currently, I am working on extracting substring from a single string using regular expressions in Snowflake. This is what I have as of now.

e.g. String = 'These are the Id's which I have WPD4567, WPD36785 also this is another id WPD111234 '

I want to extract all the substrings starting with WPD i.e. WPD4567 WPD36785 WPD111234.

I want to have these substrings in the same column or different columns.

This is what I have tried as of now:

select 
   REGEXP_SUBSTR('These are the Id's which I have WPD4567, WPD36785 also this is another id WPD111234 ','(WPD)(.*)\\d*')

Actual Output : WPD111234

Expected Output : WPD4567 WPD36785 WPD111234

snowflake-cloud-data-platform sql
1 Answers

I like the idea of solving this with a JS UDF, but this is a solution with pure SQL, using split_to_table():

with data as (
    select $1 id, $2 val, $3 s
    from values(
        1, 'x', $$These are the Id's which I have WPD4567, WPD36785 also this is another id WPD111234$$
    ),(
        2, 'y', $$WPD10101 These are the Id's which I have WPD4567, WPD36785 also this is another id WPD111234$$
    )
)

select any_value(data.id) id, any_value(data.val) val 
    , listagg('WPD' || regexp_substr(value, '[\\d]*'), ' ') wpds
from data, table(split_to_table(s, 'WPD'))
where index>1
group by seq

enter image description here

user_1324380
Related