string="[(ETLCoreB15,COB-W#2018-05-25, [ETLCoreB4,ETLCoreB15],[ETLCoreB1,ETLCoreB15]),(ETLCoreB20,COB-A#2018-05-25, [ETLCoreB8,ETLCoreB20],[ETLCoreB1,ETLCoreB20])]"
我希望使用regex_extract
输出作为最后etlcoreb1
使用split()
进行字符串拆分,regex_replace
或translate
删除某些字符。我已经评论了我在代码中每个步骤上所做的工作。解析的字符串:
select split(
translate(
split(
split(
regexp_replace(str,'^\[|\]$',''), --remove outer []
'\)\s*,')[1], --split by )<any spaces>comma and take second string, which is second struct
'\]\s*,')[1], --split by ]<any spaces>comma got [ETLCoreB1,ETLCoreB20])
'([])',''), --remove ()[] characters
',')[0] --split by comma and take first element
from
(select '[(ETLCoreB15,COB-W#2018-05-25, [ETLCoreB4,ETLCoreB15],[ETLCoreB1,ETLCoreB15]),(ETLCoreB20,COB-A#2018-05-25, [ETLCoreB8,ETLCoreB20],[ETLCoreB1,ETLCoreB20])]' as str) s
结果:
OK
ETLCoreB1
Time taken: 1.414 seconds, Fetched: 1 row(s)
希望你已经抓住了这个想法。