Extract a string using regex_substr

Question

I have a code written in DB2 that will extract the string values between / to different columns.
```
REGEXP_SUBSTR(RIGHT(TRIM('/BJ/1/10/114/201010049'),LENGTH(TRIM('/BJ/1/10/114/201010049'))-4),'^(.*?)/',1,1),LENGTH(REGEXP_SUBSTR(RIGHT(TRIM('/BJ/1/10/114/201010049'),LENGTH(TRIM('/BJ/1/10/114/201010049'))-4),'^(.*?)/',1,1))
```
From above code, the string after '/BJ/' will go to column_1, the value after '1/' will go to column_2 etc as below:
Column_1 = 1, column_2 = 10, column_3 = 114, column_4 = 202010049.
Now I am trying to do the same in redshift. but I am getting below error:

> error:  Invalid preceding regular expression prior to repetition operator.  The error occurred while parsing the regular expression: '^(.*?>>>HERE>>>)/'

Can anyone please help me to fix this?

Accepted Answer

Hello Joe,

You can try using the split_part string function as below

select
params, 
split_part(params, '/', 3) as col1,
split_part(params, '/', 4) as col3,
split_part(params, '/', 5) as col3,
split_part(params, '/', 6) as col4
from (
    select '/BJ/1/10/114/201010049' as params
) tmp

Please feel free to comment if you have further issues

Extract a string using regex_substr

相關內容