Below is a sample of the text that I am working with.
x
---
info1:
* val: "A"
---
Type:
* answers:
* - !<string>
* val: "B"
* - !<string>
* val: "C"
---
info2:
* val: "D"
---
And I am trying to select the following text:
Type:
* answers:
* - !<string>
* val: "B"
* - !<string>
* val: "C"
I was trying to use a look ahead, but haven’t had much success.
REGEXP_SUBSTR(col, 'Type:(.*---)')
Here I am trying to look up until the next occurrence of ‘—‘, but I think I misunderstanding how it works.
Advertisement
Answer
You don’t need regexp lookahead to get the string you want, it’s just eg.
REGEXP_SUBSTR(col, '(^Type:\s+(^[*].*$\s+)*)^---', 1, 1, 'm', 1)
If you need regexps with lookahead, etc, use JavaScript RegExps via a function wrapper, eg.
CREATE OR REPLACE FUNCTION RegExp_Match("STRING" VARCHAR, "REGEXP" VARCHAR)
RETURNS VARIANT LANGUAGE JAVASCRIPT STRICT IMMUTABLE AS
'return STRING.match(REGEXP);';
CREATE OR REPLACE FUNCTION RegExp_Match("STRING" VARCHAR, "RX" VARCHAR, "FLAGS" VARCHAR)
RETURNS VARIANT LANGUAGE JAVASCRIPT STRICT IMMUTABLE AS
'return STRING.match(new RegExp(RX, FLAGS));';
SELECT RegExp_Match('<aA>', '(?<=<)(.)\1(?=>)', 'i');
-- RegExp with lookback, back reference and lookahead ignoring case
=> [ "aA", "a" ]