REPLACE

Question

Post reply

REPLACE

NicHopper

SSCrazy Eights

Points: 9090
More actions
October 13, 2008 at 3:30 am

#199358

Hi all,
I am trying to remove all non alpha numeric characters from a string value, is it possible to do this with a replace, i'm sure I've seen this before, something similar to
SELECT 'This '' is a sample',
REPLACE('This '' is a sample','[^A-Z]','')
but this does not remove the ' character. Am I going mad?
Thanks,
Jackal
Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hello

GSquared demonstrates an excellent function for this here:

http://www.sqlservercentral.com/Forums/Topic470379-338-1.aspx

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 2

Hi Chris,

Thanks for that, I've had a look at that. Is it not possible to do this using a replace then?

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Yes it is - GSquared posted a tried-and-tested function using REPLACE in the same thread. Problem is, there are so many possible characters to look for.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 4

Sorry, I'm being a clown, I skimmed over the article to quickly.

I see now the usage of the replace, however this is done in a while loop.

I was wondering if it is possible to do with just one call of the replace,

something like;

SELECT 'This '' is a sample',

REPLACE('This '' is a sample','[^A-Z]','')

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Jackal (10/13/2008)
Sorry, I'm being a clown, I skimmed over the article to quickly.
I see now the usage of the replace, however this is done in a while loop.
I was wondering if it is possible to do with just one call of the replace,
something like;
SELECT 'This '' is a sample',
REPLACE('This '' is a sample','[^A-Z]','')

😀 Don't you just wish! The functions are pretty quick and they also encapsulate the process nicely, but if you don't want to use a function for this, then it's possible to achieve much the same thing by joining a tally table to your target table.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 6

No, your right it is a quick solution and meets my needs.

I'll stick with it, but I was just wondering if it was possible to do it with just a singular 'REPLACE' command.

Thanks a lot for your help.

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/