Regex: Get value between two strings in AWS Redshift

user_16535823 0
Viewed 623

How do I extract a certain value between two substrings in redshift with regex?

I have strings like the one below in a column in redshift:

[{'code': 'bla', 'amount': '149.30', 'type': 'fixed_amount'}]

I want to extract the float number. I used several regex expressions, but seems like redshift is not accepting them.

select order_id, discount_codes, regexp_substring(discount_codes, '''amount'': ''[^'']*') as value from orders_shopify_de

Gave me this error:

ERROR: function regexp_substring(character varying, "unknown") does not exist Hint

Also for example:

regexp_replace(discount_codes, '(?<=''amount'': '')(.*)(?='',)')

Gave me this error:

ERROR: function regexp_substring(character varying, "unknown") does not exist Hint

Is there a way to extract the float?

Thanks in advance!

amazon-redshift sql regex
1 Answers

You can use

REGEXP_SUBSTR(discount_codes, '''amount'': ''([^'']*)', 1, 1, 'e')

The pattern is 'amount': '([^']*) that matches

  • 'amount': ' - a 'amount': ' string
  • ([^']*) - Group 1: any zero or more chars other than ' chars.

The additional arguments in REGEXP_SUBSTR docs are:

  • 1 - start searching from the first char in the string
  • 1 - tells the regex engine to extract the first occurrence of the pattern
  • 'e' - enables extraction of the subexpression (aka capturing group value) from the resulting match.
user_38329700
Related