how to apply split_part function from end of strin

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

Answer 1

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)