SQL Server 2008 : replace string

Question

I have a table with an erroneous symbol('�') in a number of rows in one column.

The TSQL script below does not work.

UPDATE S
SET S.Offering_Details = REPLACE(S.Offering_Details, '�', '...')
FROM tblSacrifices S

The column in question has datatype of nvarchar(230) and allows null entries.

The data came from a csv file converted from Excel an d via a Visual studio windows app. The data originally was '...' but I think perhaps word/excel classed this as one character (rather than 3 separate '.'). When my application read the original string from CSV file it (unintentionally) replaced the '...' with '�' before submitting the data into the database.

PLEASE help

Answer 1

Figured it out. Thanks all for your help.

I had to convert to binary. All unicode characters above 65500 require this as normal REPLACE() doesn't work.

UPDATE S
SET S.Offering_Details = REPLACE(S.Offering_Details, nchar(65533) COLLATE Latin1_General_BIN, '...')
FROM tblSacrifices S

Answer 2

Edit:

Following update in comments try REPLACE(S.Offering_Details, nchar(65533), '...')

Original Answer:

This might help you troubleshoot it

declare @s nvarchar(230)

SELECT @s= ProblemCol
FROM YourTable
WHERE ProblemRowId = X;


set @s = N'日本国'; /*For testing*/


WITH N AS
     (SELECT 1 idx,
             LEFT(@s,1)ch,
             UNICODE(LEFT(@s,1)) C

     UNION ALL

     SELECT idx+1,
            SUBSTRING(@s,idx+1,1),
            UNICODE(SUBSTRING(@s,idx+1,1)) C
     FROM   N
     WHERE  idx<LEN(@s)
     )


  SELECT idx, ch,C
  FROM     N    

Result of Test

idx         ch   C
----------- ---- -----------
1           日    26085
2           本    26412
3           国    22269