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?

asked 25 Oct '10, 14:20

Reimer%20Pods's gravatar image

Reimer Pods
4.2k344583
accept rate: 11%

edited 15 Mar '13, 18:11

Mark%20Culp's gravatar image

Mark Culp
23.3k9132275


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.

permanent link

answered 25 Oct '10, 15:36

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.6k263661
accept rate: 17%

1

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.

(25 Oct '10, 19:26) Volker Barth

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

(26 Oct '10, 15:35) Reimer Pods

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.

permanent link

answered 25 Oct '10, 19:31

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 32%

Comment Text Removed
1

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.

(26 Oct '10, 15:34) Reimer Pods

Perhaps you can post your results.

(27 Oct '10, 10:31) Thomas Dueme...
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×101
×12

question asked: 25 Oct '10, 14:20

question was seen: 3,942 times

last updated: 15 Mar '13, 18:11