从字符串中删除某些字符

I'm trying to remove certain characters.

我试图删除某些字符。

At the moment I have output like cityname district but I want to remove cityname.

目前我有像cityname district这样的输出，但是我想删除cityname。

SELECT Ort FROM dbo.tblOrtsteileGeo
WHERE GKZ = '06440004'

Output:

输出:

Büdingen Aulendiebach
Büdingen Büches
Büdingen Calbach
Büdingen Diebach
Büdingen Dudenrod
Büdingen Düdelsheim

Desired output:

期望的输出:

Aulendiebach
Büches
Calbach
Diebach
Dudenrod
Düdelsheim

3 个解决方案

#1

You can use Replace function as;

可以使用Replace函数as;

REPLACE ('Your String with cityname here', 'cityname', 'xyz')
--Results
'Your String with xyz here'

If you apply this to a table column where stringColumnName, cityName both are columns of YourTable

如果将此应用于一个表列，其中stringColumnName、cityName都是表的列

SELECT REPLACE(stringColumnName, cityName, '')
FROM YourTable

Or if you want to remove 'cityName' string from out put of a column then

或者如果您想从列中删除'cityName'字符串

SELECT REPLACE(stringColumnName, 'cityName', '')
FROM yourTable

EDIT: Since you have given more details now, REPLACE function is not the best method to sort your problem. Following is another way of doing it. Also @MartinSmith has given a good answer. Now you have the choice to select again.

编辑:既然你已经给出了更多的细节，替换函数并不是解决问题的最好方法。下面是另一种方法。@MartinSmith也给出了一个很好的答案。现在您可以选择再次选择。

SELECT RIGHT (O.Ort, LEN(O.Ort) - LEN(C.CityName)-1) As WithoutCityName
FROM   tblOrtsteileGeo O
       JOIN dbo.Cities C
         ON C.foo = O.foo
WHERE  O.GKZ = '06440004'

#2

One issue with REPLACE will be where city names contain the district name. You can use something like.

替换的一个问题是城市名称中包含地区名称。你可以用类似的东西。

SELECT SUBSTRING(O.Ort, LEN(C.CityName) + 2, 8000)
FROM   dbo.tblOrtsteileGeo O
       JOIN dbo.Cities C
         ON C.foo = O.foo
WHERE  O.GKZ = '06440004'

#3

-2

UPDATE yourtable 
SET field_or_column =REPLACE ('current string','findpattern', 'replacepattern') 
WHERE 1

#1