extracting matching regex value as column in sql

Question

I have a column with a long string and I want to extract some matching values from string as column in SQL
I have content column which contains string in below format

workflow:myToPath{|}mypath:/add-products{amp}items[0][id]=AB6C4GBCT34DBBNGTLO{amp}valueid=AB3ESHQY{|}source:Gen:all:GenCard{|}guid:xxxx-xxx-xxxx{|}pascode:ABCD{|}version:14.0.12

I want to extract valueid, source, pascode, version as columns from content_column which contains above sample strings
I tried regexp_extract(content_action, 'promoid=\w\w\w\w\w\w\w\w',0), but this is returning blank

Answer 1

There are a few changes that you need to make:

• You must write a regex that matches the whole string - add .* before and after your matching data. Something like this

egexp_extract(column, '.*version:(.*)', 1)

• You need to define a capture group around your match using ()

• References to the capture groups are starting with 1, not with 0

I would recommend using a site like this to test your regular expressions - you need to use Java dialect there.

Answer 2

For reference, you can use the following capture pattern, to evaluate the key and value.

The first group is the key, and the second is the value.

(valueid|(?:source|pascode|version))[=:](.+?)(?=\{|$)