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

Try being explicit that the character you are searching for is nvarchar by using a leading N:

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

UPDATE Based on the discussion from Martin's answer:

I've no reason to think that this is functionally any different that what I've posted above, but you could try:

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

UPDATE 2

I didn't read Martin's code closely enough before updating my answer. Because it uses the SQL server UNICODE function to display results, the actually error character is masked by 65533.

See here for details of unicode char 65533 - it's the generic unicode value for bad data.

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

Answer 3

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