Remove certain characters from a string

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'

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'

Remove certain characters from a string

Related

Recent Posts