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

Solution 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'

Solution 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' 

Solution 3

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

Comments

[UrKll]

as soon as i can .. have to wait 4 more minutes

[Adir D]

@Kaf What is the rush? Are you worried a better answer might come along?

[Adir D]

@Kaf that's fine, but you don't need to coerce him to accept the answer after only 4 minutes have passed. <shrug>

[Adir D]

@Kaf as an example, now that we have more information about the actual requirements, the accepted answer isn't necessarily the most efficient approach. Just a suggestion: don't rush users.

[ypercubeᵀᴹ]

Or the correct one. If there is a city named Aach and a city-district named Munich Aachen, you'll get some funny result.

[Martin Smith]

@Kaf They do say "There is another table where i can get the cityname an genericly insert it into the replace function". It would have been better if they had given more information about that so we don't have to guess the missing details ourselves though.

[Kaf]

@MartinSmith: I answered 34 minutes ago, he commented that 26 minutes ago. Also pls note that was why I explained how replace function works rather than directly answering the question.

[Adir D]

@Kaf we're not giving you grief about your answer. But your rush to have him accept your answer now makes the accepted answer less than optimal. See Jeff Atwood's answer here.

[Kaf]

@AaronBertrand: That is fair enough, also honestly I didn't rush him, I just wanted to let him know as he was a new user. I have seen there are so many people comment like that. Also I understand, given the correct details, replace function is not the best way to do it. I can take the answer off...

[UrKll]

guys calm down ... i know that there are districts with the same name like cities but that isn't something important as i only select the cities / district by the postcode

[Kaf]

@UrKll: I have edited my answer with a better method to sort your problem. Also it gives you the chance to select Martin's answer if you wish to do so.

[Kaf]

@AaronBertrand and MartinSmith: I edited accepted answer to improve and to give a fair chance to the user to select a better one.

[ypercubeᵀᴹ]

@Kaf: I see. My comment was not a rant about your answer. It may be good when posted, with the info known at the time. I commented so you either correct it, now that there is more info (as you did) or the future visitor is warned.