Replacing a specific Unicode Character in MS SQL S

问题:

I'm using MS SQL Server Express 2012.

I'm having trouble removing the unicode character U+02CC (Decimal : 716) in the grid results. The original text is 'λeˌβár'.

I tried it like this, it doesn't work:

SELECT ColumnTextWithUnicode, REPLACE(ColumnTextWithUnicode , 'ˌ','') 
FROM TableName

The column has Latin1_General_CI_AS collation and datatype is nvarchar. I tried changing the collation to something binary, but no success as well:

SELECT ColumnTextWithUnicode, REPLACE(ColumnTextWithUnicode collate Latin1_General_BIN, 'ˌ' collate Latin1_General_BIN,'') 
FROM  TableName

Or even using the NChar() function like:

SELECT ColumnTextWithUnicode, REPLACE(ColumnTextWithUnicode , NCHAR(716),'') 
FROM TableName

The results are 'λeˌβár' for all three.

But if I cast the column to varchar like:

SELECT ColumnTextWithUnicode, REPLACE(CAST(ColumnTextWithUnicode as varchar(100)), 'ˌ','') 
FROM TableName

the result becomes 'eßár', removing both the first character and 'ˌ'.

Any ideas to remove just the 'ˌ'?

回答1:

you just need to put N before string pattern too (if you want look for unicode char):

SELECT REPLACE (N'λeˌβár' COLLATE Latin1_General_BIN, N'ˌ', '')