SQL Server 2008:替换字符串 [英] SQL Server 2008 : replace string

问题描述

我有一个表格，其中一列的多行中有一个错误符号(' ').

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

下面的 TSQL 脚本不起作用.

The TSQL script below does not work.

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

相关列的数据类型为 nvarchar(230) 并允许空条目.

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

数据来自通过 Visual Studio Windows 应用程序从 Excel 和 d 转换而来的 csv 文件.数据最初是..."，但我认为 word/excel 可能将其归类为一个字符(而不是 3 个单独的.").当我的应用程序从 CSV 文件读取原始字符串时，它(无意中)在将数据提交到数据库之前将..."替换为"".

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.

请帮忙

推荐答案

想通了.感谢大家的帮助.

Figured it out. Thanks all for your help.

我不得不转换为二进制.65500 以上的所有 unicode 字符都需要这样做，因为正常的 REPLACE() 不起作用.

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

这篇关于SQL Server 2008:替换字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！