从第n个分割到字符串结尾的split_part函数
问题描述:
第二个空格后,我需要取值直到字符串中的特定位置。 来源:从第n个分割到字符串结尾的split_part函数
"8 115 MACKIE STREET VICTORIA PARK WA 6100 AU"
"6A CAMBOON ROAD MORLEY WA 6062 AU"
输出:
"MACKIE STREET VICTORIA PARK"
"CAMBOON ROAD MORLEY"
我试图从单元#分裂街道名称和郊区,街道#出现在开头和状态,邮编,目前国结束。
答
t=# with s(v) as (values('6A CAMBOON ROAD MORLEY WA 6062 AU'),('8 115 MACKIE STREET VICTORIA PARK WA 6100 A'))
, split as (select *,count(1) over (partition by v) from s, regexp_matches(v,'([A-Z]+)','g') with ordinality t(m,o))
select distinct v,string_agg(m[1],'') over (partition by v) from split where o <= count-(3-1);
v | string_agg
---------------------------------------------+------------------------------
8 115 MACKIE STREET VICTORIA PARK WA 6100 A | MACKIE STREET VICTORIA PARK
6A CAMBOON ROAD MORLEY WA 6062 AU | CAMBOON ROAD MORLEY
(2 rows)
我排除索引(或任何不嵌合掩模[A-Z]+
),从而切割从所述端不三个位置,而是两个(3-1)
其中1是提前公知的指数。
另外,我不从第二个空间开始,因为它会违背你想要的结果...