CODEWITHSUNDEEP
regexamazon-redshift
scott martin
scott martin

Reputation: 1293

Redshift - Extracting data based on pattern

I am trying to extract a sub-string from the below text using a specific pattern in Redshift.

value
proda_cat1_subcat_a_jan2020
cat2_subcat_b_prodb_jan2020

I am trying to extract the string cat1_subcat_a and cat2_subcat_b from the above strings.

I tried doing regexp_substr(value,'cat[^_]') but this only pulls the string till the first underscore

Expected output:

value, output
proda_cat1_subcat_a_jan2020, cat1_subcat_a
cat2_subcat_b_prodb_jan2020, cat2_subcat_b

Upvotes: 0

Views: 416

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

Try using the following regex pattern:

cat[0-9]+_[^_]+_[^_]+

SQL query:

SELECT
    value,
    REGEXP_SUBSTR(value, 'cat[0-9]+_[^_]+_[^_]+') AS output
FROM yourTable;

Demo

The demo is in Oracle, but the syntax should also work on Redshift.

Upvotes: 1

Related Questions