I have quite a strange problem. I have a column with surnames with national characters I would like to replace national characters, so I made something like that

select replace(surname,'Ê','E') 

for surname ABCDÊ as result still is ABCDÊ but when I make a test and replace the value that I copied

select replace('ABCDÊ','Ê','E') 

It works correctly and as result I get ABCDE

CodePudding user response：

This sort of issue usually comes down to how SQL stores unicode characters.

Try running the following to see what sort of output you get. On my server both work fine, but it may well help you identify your issue.

DECLARE @table AS TABLE ( surname VARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL )

INSERT INTO @table ( surname )
VALUES ( 'ABCDÊ' )
,      ( N'ABCDÊ' )

SELECT  surname
,       REPLACE(surname, 'Ê', 'E')
  FROM  @table

CodePudding user response：

Feel like something is missing here. Is it possible your column is using a case sensitive collation? If so, either will have to override it, or just replace each letter case individually (probably the better method to preserve original letter case).

Adjusting for Case-Sensitive Collation

/*Collation abbreviation "CS" = Case-sensitive*/
DECLARE @table AS TABLE (surname VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS)
INSERT INTO @table
VALUES ('abcdê'),('ABCDÊ')

SELECT  surname
        ,YourOriginalCode = REPLACE(surname, 'Ê', 'E')          
        ,ForceCaseInsensitiveCollation = REPLACE(surname COLLATE SQL_Latin1_General_CP1_CI_AS , 'Ê', 'E') 
        ,ReplaceForEachLetterCase = REPLACE(REPLACE(surname,'Ê','E'),'ê','e') 
        ,SQLServer2017Version = TRANSLATE(surname,'êÊ','eE')
FROM  @table

Results