时间戳

首页 > 解决方案 > SQL 解析字符串由 | 分隔 进入键和值

sql - SQL 解析字符串由 | 分隔 进入键和值

问题描述

我需要帮助解析由管道分隔的字符串。第一个数字是键，管道之后的后续字符串是该键的数百个值

|01|00109394|05|84|08|34353637

这应该被分解为第一个管道值之后的长度= 2 数字的键：在每个键之后

脚本应该打破字符串并分解为行，以便“key”列将具有所有键，“values”列将具有所有所有值

样本输出

KEY Value
01  00
01  10
01  93
01  94
05  84
08  34
08  35

这是我写但不工作的代码

Select my_key, explode(str_to_map(my_key,'[|]','[|]')) as (Key, Value)
from test_table;

请建议如何解析这个字符串

标签： sqlparsinghivesplithiveql

解决方案

我用分裂和爆炸两次做到了。第一次获取未拆分的键和值，第二次将值分隔两个字符。阅读代码中的注释：

with your_data as (
select stack (1, '|01|00109394|05|84|08|34353637') as str
)

select --s.initial_str, 
       s.key, v.val
from
(
select s.pos, s.initial_str, s.key, s.val
from
(
select s.initial_str,
       s.pos, --0 and even positions are keys, odd are values
       s.val as key, 
       --assign the value to each key, which is the next eploded value 
       lead(val,1) over(partition by s.initial_str order by s.pos) as val --some keys from main table are in partition by clause
from       
( --explode string
select d.str initial_str, s.pos, s.val
  from your_data d lateral view outer posexplode(split(regexp_replace(str,'(^\\|)|(\\|$)',''),'\\|')) s as pos, val --remove leading and trailing pipe and explode
) s
)s
where s.pos%2=0 --filter keys with assigned values only (0 and even are rows we need)
) s 
--explode each two chars
--(?<=\\G.{2}) matches an empty string that has the last match (\\G) followed by two characters (.{2}) before it (?<=)
lateral view outer explode(split(s.val,'(?<=\\G.{2})')) v as val 
where v.val!=''
;

结果：

01      00
01      10
01      93
01      94
05      84
08      34
08      35
08      36
08      37

推荐阅读