Patindex for characters with odd ascii values

[sagn]

OK.. I have a large amount of data coming from
european sources. The data are text data but they should be
numbers. While going through these data I have found some
'typos' where there exist values such as CHAR(215).
of course trying to do any sort of convert or cast into a float fails here.

I would like to be able to pull out the records that have some ascii value withint the range of 191 and 255, for example. I have tried using patindex in this manner:

<<
patindex ('%[¿-ÿ]%',textfield)
>>
where the characters ¿ and ÿ were obtained by having query analyzer print out the char value for the ascii values I inserted
(first 191 ¿ , last 255 ÿ)

I always get 1 as an answer. No matter what my textfield is.

Charindex seems to work, but this is not desirable as I can only do one at atime.

What am I missing here?
Thanks a lot

 

[gmmastros]

The field is supposed to contain numbers? You could try finding the first character that is not a number. The code I show is a 'kick start' in the right direction. It will most likely need some modification to get it right.

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('abc¿')
Insert Into @Temp Values('12345678ÿ')
Insert Into @Temp Values('-12345.678')


Select PatIndex('%[^0-9-+.]%', Data) From @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sagn]

Hmm That's a thought. A good one at that.

There are all sorts of typos and I have been explicitly looking for
what is not supposed to be there. You idea is an implicit approach. I like it

I will give it a shot!