Regex in redshift

Question

I have a problem.. I need to extract from this field:

exchange<=><br>type<=>full<br>cont<=>part<br>req<=>no<br>money<=>money<br>money<=>3100,4000,0,month<br>boss<=>0

five informations:

full
part
3100 4.4000 5.month

I have tried to use regexp_substr(): regexp_substr(column,'type<=>[^<br>]*') but I dont have any knowledge about regex and I cant do it in a properly way.. can you help me with that?

Try `regexp_substr(column,'type<=>([a-zA-Z0-9]+)')` or try `[^<]+` instead of `[a-zA-Z0-9]+`. — Wiktor Stribiżew, Sep 10 '18 at 16:51
yes it works for type and cont :) my results (type<=>full) and (cont<=>part), but it doesnt work from 3100, 4000 and month.. — monika_w091, Sep 10 '18 at 16:56
Aha, so you want separate regexps, right? `regexp_substr(column,'type<=>([^<]+)')`, `regexp_substr(column,'cont<=>([^<]+)')`, then `money<=>([0-9]+)`, `money<=>[0-9]+,([0-9]+)` and `money<=>[0-9]+,[0-9]+,[0-9]+,([^<]+)` — Wiktor Stribiżew, Sep 10 '18 at 17:10
when it comes to : `money<=>([0-9]+)` <- it works properly but others not.. when it comes to `4000` my input : `money<=>3500,4000` and when it comes to `month` my input : `money<=>3100,4000,0,month` — monika_w091, Sep 10 '18 at 17:19
Are you sure you are placing the capturing parentheses where I put them? — Wiktor Stribiżew, Sep 10 '18 at 17:20
Well, see https://regex101.com/r/o6vCPT/1, https://regex101.com/r/o6vCPT/2 and https://regex101.com/r/o6vCPT/3 - the green highlighted text must be what you get. — Wiktor Stribiżew, Sep 10 '18 at 17:24
Useful for Redshift: [Extract data in parentheses with Amazon-redshift](https://stackoverflow.com/q/54922350/174777) — John Rotenstein, Mar 01 '19 at 00:53

score 0 · Answer 1 · edited Sep 11 '18 at 02:37

0

I never worked with redshift but with regex I can help you:

"(type|cont|money)<=>([^<,]+)(,([^<,]+),[^<,]+,([^<,]+))?"

The capture number 4 in the string you put as an example it will capture all you need, it even exclude the 0 :

Group 1: money
Group 2: 3100
Group 3: ,4000,0,month Group 4: 4000
Group 5: month

In case you have problems, tell me. If you want to master your regex skills I can teach you, it will be useful.

edited Sep 11 '18 at 02:37

nhahtdh

55,989
15
126
162

answered Sep 10 '18 at 17:28

YOGO

531
4
5

wait, I will fix it – YOGO Sep 10 '18 at 17:43
Look at this link, https://stackoverflow.com/questions/7758859/how-to-extract-group-from-regular-expression-in-oracle You have to specify the capturing group you want, in that case is the second , I gess, because I can not try this regex in redshift – YOGO Sep 10 '18 at 17:56
In the last case you need to specify that you want the group 2 , 4 and 5 – YOGO Sep 10 '18 at 17:59
Here is explained better : https://www.w3resource.com/oracle/character-functions/oracle-regexp_substr-function.php – YOGO Sep 10 '18 at 18:04

Regex in redshift

1 Answers1