Extract data in parentheses with Amazon-redshift

Question

I have these characters in a table:

LEASE THIRD-CCP-MANAGER (AAAA)
THE MANAGEMENT OF A THIRD PARTY (BBBB / AAAA)

When I extract the information:

AAAA
BBBB/AAAA

That is, I have to look for the pattern and extract what is inside the parenthesis.

I'm trying to use the REGEXP_SUBSTR function.

In amazon redshift, how do I extract the characters in parentheses?

thanks

Answer 1

Whoa, that was hard!

Here is the syntax to use:

SELECT REGEXP_SUBSTR('One (Two) Three', '[(](.*)[)]', 1, 1, 'e')

This will return: Two

It appears that escaping brackets with \( doesn't work, but putting them in [(] does work. The 'e' at the end will "Extract a substring using a subexpression".

Answer 2

You’re probably struggling with () meaning something in regular expressions (lookup “back references”)

To tell regex that you just mean the characters ( and ) without their special meaning, “escape” them using \

regexp_substr(yourtable.yourcolumn,'\(.*\)')

Answer 3

use position for finding the index of parenthesis ( and then substring

select 
substring(position('(' in 'LEASE THIRD-CCP-MANAGER (AAAA)'),position(')' in 'LEASE THIRD-CCP-MANAGER (AAAA)'))

or you can use split_part

split_part('LEASE THIRD-CCP-MANAGER (AAAA)','(',2)