CODEWITHSUNDEEP
sql-servert-sqlreplaceunicode-string
Noble_Bright_Life
Noble_Bright_Life

Reputation: 599

Replacing a specific Unicode Character in SQL Server

I'm using 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 'ˌ'?

Upvotes: 3

Views: 27540

Answers (2)

Darka
Darka

Reputation: 2768

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

Upvotes: 12

JHP
JHP

Reputation: 11

It is working fine by following select query as we are getting U+FFFD � REPLACEMENT CHARACTER when we bulk inserting address filled from txt to sql.

select Address, REPLACE(Address COLLATE Latin1_General_BIN,N'�',' ')  from #Temp

Upvotes: 0

Related Questions