Snowflake SQL, how to lookahead until a certain occurrence of a value

Below is a sample of the text that I am working with.

--- 
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.

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" ]