[FIXED] How can I Delete, replace the data of a field in postgresql to simplify them?

Issue

I am stuck at this point, I am trying to clean the row name of a table using postgresql. The table is for districts in wales and England.

What I am trying is to delete the strings 'District', '(B)', 'London Boro' and everything before the hyphen in the dual English - Welsh name, for example (Swansea - Abertawe) I would like to eliminate everything before the hyphen (I would like to know how to do it in both direction anyway) and get (Abertawe). I manage with the first 3 strings but with the part of hyphen I cannot find the solution to the issue.

I have tried using replace and trim but I don't obtain the result desired.

This is code as I have tried the last time:

select name,
trim(replace(replace(replace(replace(name, 'District', ''), '(B)', ''), 'London Boro', ''),'% - %', ''), ' - ') 
from district;

This is a sample of the table used

    name
    Swansea - Abertawe
    Barnsley District (B)
    Bath and North East Somerset
    Bedford (B)
    Birmingham District (B)
    Blackburn with Darwen (B)
    Blackpool (B)
    Blaenau Gwent - Blaenau Gwent
    Bolton District (B)
    Bournemouth (B)
    Bracknell Forest (B)
    Bradford District (B)
    The Vale of Glamorgan - Bro Morgannwg
    Aylesbury Vale District
    Chiltern District
    South Bucks District
    Wycombe District
    Bury District (B)
    Cardiff - Caerdydd
    Caerphilly - Caerffili
    Calderdale District (B)
    Cambridge District (B)
    East Cambridgeshire District
    City of Westminster London Boro
    Croydon London Boro
    Ealing London Boro
    Enfield London Boro
Castell-nedd Port Talbot - Neath Port Talbot

And this is what I want to obtain:

 name
    Abertawe
    Barnsley
    Bath and North East Somerset
    Bedford
    Birmingham
    Blackburn with Darwen
    Blackpool
    Blaenau Gwent
    Bolton
    Bournemouth
    Bracknell Forest
    Bradford
    Bro Morgannwg
    Aylesbury Vale
    Chiltern
    South Bucks
    Wycombe
    Bury
    Caerdydd
    Caerffili
    Calderdale
    Cambridge
    East Cambridgeshire
    City of Westminster
    Croydon
    Ealing
    Enfield
Neath Port Talbot

Thanks,

Solution

Use Case statement to distinguish both of the conditions and use Position function to check the - exist or not.

Try This

select
name,
case when position(' - ' in name)>0 then 
trim(replace(replace(replace(substr(name,position(' - ' in name)+3,length(name)), 'District', ''), '(B)', ''), 'London Boro', ''))
 else 
 trim(replace(replace(replace(name, 'District', ''), '(B)', ''), 'London Boro', ''))
 end 
from district

DEMO

Answered By - Akhilesh Mishra

Answer Checked By - David Marino (PHPFixing Volunteer)