A Strange issue with the REPLACE Function

Question

A Strange issue with the REPLACE Function

RedLlewy

Ten Centuries

Points: 1084
More actions
April 8, 2009 at 8:31 am

#130544

Hi All,
I am using the replace function to remove all but alpha-numeric characters and have run into a little issue with superscript numbers.
The issue can be demonstrated (at least on my server) using the following code
DECLARE @STR VARCHAR(100)
SET @STR = '1234567890'
SELECT REPLACE(@str,CHAR(179),'_')
CHAR(179) is a superscript 3 and the result I get from the REPLACE is
12_4567890
The Server is SQL 2005 Standard Edition 64-bit, version 9.00.4035.00 with a collation of Latin1_General_CI_AS and language set to English(United States)
Any help or explanations would be gratefully received.
Llewy
RedLlewy
"The Plural of Anecdote is not Data"

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

I don't know how the replace function behaves in different collations, but I know the problem is related with different collations.

Let' see it..

DECLARE @STR VARCHAR(100)

SET @STR = '1234567890'

SELECT REPLACE( @STR, CHAR(179) COLLATE SQL_Latin1_General_CP1_CI_AS, '_' ) AS Using_SQL_Latin1_General_CP1_CI_AS,

REPLACE( @STR, CHAR(179) COLLATE Latin1_General_CI_AS, '_' ) AS [Using_Latin1_General_CI_AS],

CHAR(179) AS Replacing_Character

Results:

Using_SQL_Latin1_General_CP1_CI_AS|Using_Latin1_General_CI_AS|Replacing_Character

1234567890|12_4567890|³

--Ramesh

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

The issue is due to your using a case insensitive collation. Look at the following:

DECLARE @STR VARCHAR(100)

SET @STR = '1234567890'

SELECT REPLACE(@str collate Latin1_General_CI_AS,CHAR(179),'_')

SELECT REPLACE(@str collate Latin1_General_CS_AS,CHAR(179),'_')

SELECT REPLACE(@str collate Latin1_General_BIN2,CHAR(179),'_')

RedLlewy Ten Centuries Points: 1084 More actions · Answer 3

Thanks very much for that!

Does this imply that the REPLACE function treats CHAR(179) (superscript '3') as an Uppercase '3'?

RedLlewy
"The Plural of Anecdote is not Data"

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

It isn't REPLACE that is doing this, it is your collation. In a case-insensitive collation 'a' = 'A'. As shown by your own example, in a case-insenstive collation there is no difference between a superscript 3 and a normal 3.

RedLlewy Ten Centuries Points: 1084 More actions · Answer 5

Thank you very much for your help.

RedLlewy
"The Plural of Anecdote is not Data"