sql - Snowflake SQL，如何向前看直到某个值出现

Question

下面是我正在处理的文本示例。

--- 
info1:
*  val: "A"
---
Type:
*  answers:
*  - !<string>
*    val: "B"
*  - !<string>
*    val: "C"
--- 
info2:
*  val: "D"
---

我正在尝试选择以下文本:

Type:
*  answers:
*  - !<string>
*    val: "B"
*  - !<string>
*    val: "C"

我试图使用前瞻性，但没有取得太大的成功。REGEXP_SUBSTR(col, '类型:(.*---)')在这里，我试图查找直到下一次出现“---”，但我认为我误解了它的工作原理。

Answer 1

REGEXP_SUBSTR在雪花中相当有限，在 native SQL 中，因此当您告诉是匹配多行并通过 REGEXP_SUBSTR(t, '(Type:.*)---',1,1,'mes', 1) regexp 是 greddy 因此:

SELECT '--- 
info1:
*  val: "A"
---
Type:
*  answers:
*  - !<string>
*    val: "B"
*  - !<string>
*    val: "C"
--- 
info2:
*  val: "D"
---' as t
,REGEXP_SUBSTR(t, '(Type:.*)',1,1,'mes',1) as r1 
,REGEXP_SUBSTR(t, '(Type:.*)---',1,1,'mes',1) as r2;

给你太多的数据:

Type:
*  answers:
*  - !<string>
*    val: "B"
*  - !<string>
*    val: "C"
--- 
info2:
*  val: "D"

所以一个想法是，如果 --- 始终是一个部分标记，则先在其上进行字符串拆分，然后再进行正则表达式

WITH input as (
select '--- 
info1:
*  val: "A"
---
Type:
*  answers:
*  - !<string>
*    val: "B"
*  - !<string>
*    val: "C"
--- 
info2:
*  val: "D"
---' as t
)
select t, c.value::string as part, REGEXP_SUBSTR(part, 'Type:.*',1,1,'mes') as r1 
from input,
     lateral flatten(input=>split(t, '---')) c;

给予

T   PART    R1
---   info1:  *  val: "A"  ---  Type:  *  answers:  *  - !<string>  *    val: "B"  *  - !<string>  *    val: "C"  ---   info2:  *  val: "D"  ---        
---   info1:  *  val: "A"  ---  Type:  *  answers:  *  - !<string>  *    val: "B"  *  - !<string>  *    val: "C"  ---   info2:  *  val: "D"  ---       info1:  *  val: "A"      
---   info1:  *  val: "A"  ---  Type:  *  answers:  *  - !<string>  *    val: "B"  *  - !<string>  *    val: "C"  ---   info2:  *  val: "D"  ---      Type:  *  answers:  *  - !<string>  *    val: "B"  *  - !<string>  *    val: "C"      Type:  *  answers:  *  - !<string>  *    val: "B"  *  - !<string>  *    val: "C"  
---   info1:  *  val: "A"  ---  Type:  *  answers:  *  - !<string>  *    val: "B"  *  - !<string>  *    val: "C"  ---   info2:  *  val: "D"  ---       info2:  *  val: "D"      
---   info1:  *  val: "A"  ---  Type:  *  answers:  *  - !<string>  *    val: "B"  *  - !<string>  *    val: "C"  ---   info2:  *  val: "D"  ---        

您应该能够从中取得进步。

此外，如果您需要非常复杂的正则表达式，您可以制作一个 javascript-UDF 并使用 javascript 正则表达式引擎..