Remove certain characters from a string

Question

I'm trying to remove certain characters.

At the moment I have output like cityname district but I want to remove 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 

Answer 1

You can use Replace function 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

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

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

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.

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'