如何在postgres中从字符串末尾应用split_part函数 [英] how to apply split_part function from end of string in postgres

问题描述

我想分割下面的字符串（在单列中显示），并以空格隔开。对于下面的3行，我想要以下输出

I want to split the below string (present in a single column) separated by spaces from the end. For the below 3 rows, I want the following output

输出：

Country             STATE             STREET    UNIT
AU                  NSW               2         12
AU                  NSW                         51
AU                  NSW                         12

输入：

12 2 NOELA PLACE ST MARYS NSW 2760 AU

51 MALABAR ROAD SOUTH COOGEE NSW 2034 AU

12 LISTER STREET WINSTON HILLS NSW 2153 AU

INPUT:
12 2 NOELA PLACE ST MARYS NSW 2760 AU
51 MALABAR ROAD SOUTH COOGEE NSW 2034 AU
12 LISTER STREET WINSTON HILLS NSW 2153 AU

推荐答案

当然，这种条件解析是不可靠：

of course such conditional parsing is not reliable:

t=# with v(a) as( values('12 2 NOELA PLACE ST MARYS NSW 2760 AU')
,('51 MALABAR ROAD SOUTH COOGEE NSW 2034 AU')
,('12 LISTER STREET WINSTON HILLS NSW 2153 AU')
)
select reverse(split_part(reverse(a),' ',1)), reverse(split_part(reverse(a),' ',3)), case when split_part(a,' ',2) ~ '\d' then split_part(a,' ',2) end st, split_part(a,' ',1) un from v;
 reverse | reverse | st | un
---------+---------+----+----
 AU      | NSW     | 2  | 12
 AU      | NSW     |    | 51
 AU      | NSW     |    | 12
(3 rows)

这篇关于如何在postgres中从字符串末尾应用split_part函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！