Replacing a specific Unicode Character in MS SQL Server

sql-server replace unicode-string
Noble_Bright_Life picture Noble_Bright_Life · Jun 13, 2014 · Viewed 14.9k times · Source

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 'ˌ'?

Answer

Darka picture Darka · Jun 13, 2014

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'ˌ', '')

Related questions

UPDATE and REPLACE part of a string

I've got a table with two columns, ID and Value. I want to change a part of some strings in the second column. Example of Table: ID Value --------------------------------- 1 c:\temp\123\abc\111 2 c:\temp\123\abc\222 3 c:\temp\123\abc\333 4 c:\temp\123\…

Read More
SQL Server: How do you remove punctuation from a field?

Any one know a good way to remove punctuation from a field in SQL Server? I'm thinking UPDATE tblMyTable SET FieldName = REPLACE(REPLACE(REPLACE(FieldName,',',''),'.',''),'''' ,'') but it seems a bit tedious when …

Read More
In SQL Server, replace a Char(0), the null character, embedded in a string with its hex code

What is a construct in SQL Server T-SQL that will replace a Char(0), the null character, embedded in a string with its hex code? I.e. REPLACE('t'+Char(0)+'t', Char(0), REPLACE(master.dbo.fn_varbintohexstr(0), '000000', '')) does …

Read More