CODEWITHSUNDEEP
mysqlsqldatabasereplacetrim
GoSmash
GoSmash

Reputation: 1108

Trim multiple trailing and leading keyword off string MYSQL

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   |
 ---------

Upvotes: 3

Views: 8691

Answers (2)

Seyma Kalay
Seyma Kalay

Reputation: 2861

how about, trimming multiple characters at once ?

 SELECT colName, TRIM(colName,'North South East West') as MyNew.colName
    FROM DataTable

Upvotes: 0

Rushabh Master
Rushabh Master

Reputation: 490

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

Upvotes: 6

Related Questions