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
3 Answers
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'
UPDATE yourtable
SET field_or_column =REPLACE ('current string','findpattern', 'replacepattern')
WHERE 1
