SQL Server 2008 : replace string

问题:

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

回答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

回答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

回答3:

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.

回答4:

Use the Unicode function like this, it might help you:

UPDATE S
SET S.Offering_Details = REPLACE(S.Offering_Details, NCHAR(UNICODE('�')), '...')
FROM tblSacrifices S where S.Offering_Details like '%�%'