> The ISNUMERIC() function should quickly help you strip out your
> offending/non offending candidates. Then while it's not a performance
> god, you could loop between i=0 and 26 and replace CHAR(i+ 65) (I
> think it's 65, you'll need to check).
> However the question which you will need to clarify is what do the
> other characters mean? if they are entered in error, was it in place
> of a digit, in which case is your data reliable. If they were entered
> to mean something then surely that meaning needs to be translated?
For the most part the extra char are either - between numbers or ext.
or just space. The others I have seen are F and M, I am sure this was
integrity of the data for the vast majority.
On 6 Mar, 20:03, "Jeffrey42" <jjohnso
It sounds like this is one for minds far greater than mine. In my
opinion SQL server is not the place to be altering strings, the
functions are not particularly flexible, and such manipulation does
not lend itself to a declarative approach. I've seen some really
clever work arounds to strip out or insert particular characters,
however the problem you've encountered is that you need to perform a
lot of operations on the same field. This either lends itself to a
complex UDF, or a load of replace as you originally stated. I assume
you are outputting the data to some kind of reporting tool, it is
possible that there's more flexible behaviour in there for applying
regular expressions or similar. Of course I'm only familiar with 2000,
hopefully someone will say "ah, but there's a REGEX function in
2005!", but I wouldn't know.
All the best with your problem,