Jeff Moden (2/18/2010)
...
Lowell's code does that correctly.
Well, not exactly. The second version of StripHighAscii (the one with the loop) can give you very odd results depending on the collation being used. I use collation Latin1_general_CI_AS and
select dbo.StripHighAscii('a123²³') give me 'a' as a result, because the replace function does a case insensitive comparision. This can be fixed by changing the collation of the search character
...
SET @originaltext = replace(@originaltext,CHAR(@i) collate Latin1_General_BIN,'')
...
Check out the following script to see what happens when using a certain collation
with Tally as
(
select top 256
number - 1 N
from
master..spt_values
where
type = 'P'
)
select
t1.N, char(t1.N), t2.N, char(t2.N)
from
Tally t1
cross join
Tally t2
where
t1.N >= 128
and t2.N < 128
and char(t1.N) = char(t2.N)
Peter