Question Re: How do I remove special characters from a string without using regular expressions ( SQLA 9) in Technology Q&A

How do I remove special characters from a string without using regular expressions ( SQLA 9)

reimer_pods — Mon, 25 Oct 2010 14:20:58 GMT

When importing data I need to remove a set of characters from a string to allow comparison with a column. The database is 9.0.2, so regular expressions are not available (at least to my knowledge).

Example: String is 'A.628.164-09/11'
Remove all occurrences of '.,;-/'
Result 'A6281640911'

My first approach was to loop over all characters of the original string, using CHARINDEX to determine if it is to be skipped.

But I wonder, if there's a better solution. Any ideas?

Re: How do I remove special characters from a string without using regular expressions ( SQLA 9)

thomas_duemesnil — Mon, 25 Oct 2010 15:36:44 GMT

What about the replace() function ? If you have only a small number of characters to remove you can use a few hard coded calls which should be faster as a loop.

Re: How do I remove special characters from a string without using regular expressions ( SQLA 9)

VolkerBarth — Mon, 25 Oct 2010 19:26:02 GMT

That's the way I would do this, too, when there are not too many characters - usually as a chain of REPLACE() calls. And for performance reasons, I would think about using a LIKE '%[..]%' comparison beforehand to find out if any unwanted character exists in the string.

Re: How do I remove special characters from a string without using regular expressions ( SQLA 9)

VolkerBarth — Mon, 25 Oct 2010 19:31:03 GMT

Reimer, a similar question can be found here (more in the question than in answers), as an example of using several nested REPLACE() calls, like Thomas has suggested.

Re: How do I remove special characters from a string without using regular expressions ( SQLA 9)

reimer_pods — Tue, 26 Oct 2010 15:34:22 GMT

I've used nested REPLACE-calls in some cases. For this one the string with chars to cut out should be a configurable string. So some kind of loop seems unavoidable to me. But I could try looping over the string with skip-chars calling REPLACE for each single-char substring and measure, which version well be faster with the customers data.

Re: How do I remove special characters from a string without using regular expressions ( SQLA 9)

reimer_pods — Tue, 26 Oct 2010 15:35:04 GMT

Thank you both for your proposal. See my comment on Volkers's answer.

Re: How do I remove special characters from a string without using regular expressions ( SQLA 9)

thomas_duemesnil — Wed, 27 Oct 2010 10:31:23 GMT

Perhaps you can post your results.