演示

（with子句仅用于演示）
假设角色 % 以及 & 不要出现在文本中

with    t as 
        (
            select  stack
                    (
                        2
                       ,'USA WEST',array('NUMBER','Street','City'),array('135','Pacific','Irvine')
                       ,'USA WEST',array('NUMBER','Street','City'),array('1672','Madison','Denver')
                    ) as (c1,a1,a2)
        )
select  c1
       ,str_to_map
        (
            substring_index
            (
                regexp_replace
                (
                    concat_ws('%',a1,a2,'')
                   ,'(?<e1>.*?)%(?=((?<e2>.*?)%){3})'
                   ,'${e1}%${e2}&'
                )
               ,'&'
               ,size(a1)
            )
           ,'&'
           ,'%'
        )   as `map`
from    t
;

+----------+------------------------------------------------------+
|    c1    |                         map                          |
+----------+------------------------------------------------------+
| USA WEST | {"NUMBER":"135","Street":"Pacific","City":"Irvine"}  |
| USA WEST | {"NUMBER":"1672","Street":"Madison","City":"Denver"} |
+----------+------------------------------------------------------+

使用ascii值为1和2的字符也是一样的。

with    t as 
        (
            select  stack
                    (
                        2
                       ,'USA WEST',array('NUMBER','Street','City'),array('135','Pacific','Irvine')
                       ,'USA WEST',array('NUMBER','Street','City'),array('1672','Madison','Denver')
                    ) as (c1,a1,a2)
        )
select  c1
       ,str_to_map
        (
            substring_index
            (
                regexp_replace
                (
                    concat_ws(string(unhex(1)),a1,a2,'')
                   ,concat('(?<e1>.*?)',string(unhex(1)),'(?=((?<e2>.*?)',string(unhex(1)),'){3})')
                   ,concat('${e1}',string(unhex(1)),'${e2}',string(unhex(2)))
                )
               ,string(unhex(2))
               ,size(a1)
            )
           ,string(unhex(2))
           ,string(unhex(1))
        )   as `map`
from    t
;

+----------+------------------------------------------------------+
|    c1    |                         map                          |
+----------+------------------------------------------------------+
| USA WEST | {"NUMBER":"135","Street":"Pacific","City":"Irvine"}  |
| USA WEST | {"NUMBER":"1672","Street":"Madison","City":"Denver"} |
+----------+------------------------------------------------------+