Trim multiple trailing and leading keyword off string MYSQL

Question

I have a table with data something like this

 ---------------
|town           |
 ---------------
|Longton North  |
|Longton South  |
|Longton North  |
|Longton East   |
|Longton West   |
|East Valley    |
|West Valley    |
 ---------------

I know how to trim trailing and leading chars from string by using

TRIM(BOTH 'North' FROM town)

But I want to trim North, South, West, East out of my result. So the output should be like

 ---------
|town     |
 ---------
|Longton  |
|Longton  |
|Longton  |
|Longton  |
|Longton  |
|Valley   |
|Valley   |
 ---------

Answer 1

Try this.. this will work for you any way..

select 
trim(TRIM(BOTH 'South' FROM TRIM(BOTH 'North' FROM TRIM(BOTH 'East' FROM TRIM(BOTH 'West' FROM town))))) from tbl

or more specified

select trim(case 
when position('North' in town) > 0 then TRIM(BOTH 'North' FROM town) 
when position('South' in town) > 0 then TRIM(BOTH 'South' FROM town) 
when position('East' in town) > 0 then TRIM(BOTH 'East' FROM town) 
when position('West' in town) > 0 then TRIM(BOTH 'West' FROM town) end)
from tbl