如何在配置单元中获取管道分隔符之后的第n个字符串

使用split函数,如下所示:

with your_data as (
select stack(4,
'john:12|doe|google|usa|google.com|newspaper - title - 1 - volume - 1234|360671191',
'john:34|doe|fb|usa|google.com|newspaper - title - X - volume - 1233|360671192',
'john:45|doe|twitter|usa|google.com|newspaper - title - Y - volume - 1232|360671193',
'jane:45:1323'
) as str
)

select nvl(splitted_str[5], original_str) result
 from
(
select split(str,'\\|') splitted_str, str original_str 
  from your_data
)s;

返回:

newspaper - title - 1 - volume - 1234   
newspaper - title - X - volume - 1233   
newspaper - title - Y - volume - 1232   
jane:45:1323