The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.1k324278
accept rate: 12%

edited 15 Mar '13, 18:11

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262


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.4k203357
accept rate: 16%

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
29.3k287438645
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:

×95
×12

question asked: 25 Oct '10, 14:20

question was seen: 3,735 times

last updated: 15 Mar '13, 18:11