mysql - 修剪字符串MYSQL的多个尾随和前导关键字

我有一张桌子上面有这样的数据

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

我知道如何使用

TRIM(BOTH 'North' FROM town)

但我想从我的结果中删掉北，南，西，东。所以输出应该是

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

最佳答案

试试这个..不管怎样，这对你都有用。

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

或更详细

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

关于mysql - 修剪字符串MYSQL的多个尾随和前导关键字，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41017326/