Redshift regexp match

user_4046274 0
Viewed 434

There are values in a column like these

NEW_COLUMN
abcd-2020-ab1-01
abcd-2020-ab2-010
abcd-2021-ab1-020
abcd-2021-ab2-125
abcd-2021-ab2-126 abcd-2021-ab2-126 abcd-2021-ab2-126 -- matches all of them
abcd-2021-ab2-126-BC-DEG -- fails to match

Problem

I'm able to match the first 4 records correctly.

  1. With 5th one, it gets all instances. However I wish to get just the first one.

  2. If I extend my regexp to match this one, the original one fails to match. Is there a way do an 'OR' kind of match in regexp? Like match the first pattern or the second one.

My Code

select my_column, regexp_substr(my_column,'[[:word:]]{4}\-(.*)\-[[:digit:]]{1,4}') as extracted
from my_table

To solve first point I have tried using (.*?) instead of (.*) but redshift throws error as it does not support that. Any workaround will be helpful

Explanation of regex

[[:word:]]{4} - Finds first 4 characters

\- - finds -

(.*) - finds everything until next -

\- - finds -

[[:digit:]]{1,4} - finds digits between 0 to 4 characters

amazon-redshift sql regex
1 Answers

You seem to want to match the first string and then only consider your pattern as a prefix:

regexp_substr(my_column, '^[[:word:]]{4}\-([^ ]*)\-[[:digit:]]{1,4}[^ ]*')
user_11440350
Related