I have a table with address all in capital but I am trying to format it like this:

145 WELLBEING STREET, PARADE, LONDON, PP1 5PP

145 Wellbeing Street, Parade, London, PP1 5PP

How can i work with that? I have tried, converting all with initcap:

UPDATE address_table
SET field_address = initcap(field_address)

And then it pick the last piece and convert it in Uppercase:

UPDATE address_table
SET field_address = upper(regexp_replace(field_address , '^.*,', ''))

So i thought in concatenate but still i have the last piece in initcap...

I have also tried with :

SELECT field_address || upper(trim(reverse(split_part(reverse(field_address ), ',', 1)))) FROM address_table

but the results i got are like i.e.

Southridge, Newbury Hill, Hampstead Norreys, Thatcham, Rg18 0trRG18 0TR

How can i delete the last part after the last comma to convert it in capital?

CodePudding user response：

From Postgres 14 :

UPDATE address_table
   SET field_address = initcap(replace(field_address, split_part(field_address, ',', -1), '')) || split_part(field_address, ',', -1)

From Postgres 10 :

UPDATE address_table
   SET field_address = initcap(regexp_replace(field_address, '[^,] $', '')) || (regexp_match(field_address, '[^,] $'))[1]

see test result in dbfiddle.