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

[reimer_pods]

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?

[VolkerBarth]

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.

[reimer_pods]

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.

[thomas_duemesnil]

Perhaps you can post your results.

[thomas_duemesnil]

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.

[VolkerBarth]

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.

[reimer_pods]

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