how to apply split_part function from end of string in postgres

Question

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

OUTPUT:

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

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

Vao Tsun · Accepted Answer · 2017-10-15 09:31:12Z

2

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)

answered Oct 15, 2017 at 9:31

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to apply split_part function from end of string in postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related