Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Regex

I have a string:
blahblahblah(user_id:1234)blahblah5678

I can't figure out how to format my Regex to all just the integer out of the parenthesis. I need to avoid the digits outside the parenthesis.

Any help?

6 Community Answers

Dan Dow —

A couple of notes. First, doesn't have to be regex. Any string function in Matillion that will help me get the int out of the parenthesis is fine. Second, the strings before and after the parenthesis will vary in length. Third, there may or may not be digits on the left or right of the parenthesis.


Matillion Agent  

Ian Funnell —

Hi Dan,

The answer will depend on which of our products you are using… are you running this on Matillion ETL for Redshift, Snowflake or Google BigQuery?

Best regards,
Ian


Dan Dow —

Sorry, using Matillion. I figured out a way to do it, but I'm not sure its the best way: SPLIT_PART(SPLIT_PART(data1, ':', 2), ')', 1). That works, but I kind of like doing things the "right" way. Is there a better way?


Dan Dow —

Oh, and sorry, Redshift...


Matillion Agent  

Ian Funnell —

Hi Dan,

Your solution is fine (and any other SQL solution you find!). For Redshift, you could also consider REGEXP_SUBSTR to find the first occurrence of digits, for example:

REGEXP_SUBSTR(data1, '[[:digit:]]+', 1)

For Snowflake, it’s almost identical:

REGEXP_SUBSTR(data1, '[0-9]+', 1)

For BigQuery, it works since it’s the first one you need:

REGEXP_EXTRACT(data1, '\\d+')

Best regards,
Ian


Dan Dow —

Thanks!

Post Your Community Answer

To add an answer please login