Unicode到非Unicode的转换

Question

我在名为"PostalCode“的NVarchar字段中有一些Unicode字符。当我将它们转换为Varchar时，结果中有一个?。

我的代码是：

select PostalCode, cast((PostalCode) as varchar)) as val from  table

结果是：

PostalCode       |   val
053000           | 053000?

在这里，我在结果中得到了一个?。有没有办法去掉这些特殊字符？

Answer 1

这里有几点需要注意：

1. 如果你想知道到底是哪个字符，你可以将这个值转换成VARBINARY，它会给出字符串中所有字符的十六进制/二进制值，而十六进制中没有“隐藏”字符的概念：

-- 0x2008 =“标点符号”SELECT @PostalCode NVarCharValue，CONVERT(VARCHAR(20)，@PostalCode) AS VarCharValue，CONVERT(VARCHAR(20)，RTRIM( @PostalCode) ) AS RTrimmedVarCharValue，CONVERT(VARBINARY(20)，@PostalCode ) AS VarBinaryValue；

返回：

NVarCharValue VarCharValue RTrimmedVarCharValue VarBinaryValue 053000 053000? 053000? 0x3000350033003000300030000820

NVARCHAR数据存储为UTF-16，它以2字节集的形式工作。看看最后4个十六进制数字，看看隐藏的2字节集是什么，我们看到"0820“。由于Windows和SQL Server是UTF-16 Little Endian (即UTF-16LE)，因此字节顺序相反。翻转最后2个字节-- 08和20 --我们得到"2008"，这是我们通过NCHAR(0x2008)添加的“标点符号空格”。

此外，请注意RTRIM根本没有任何帮助，您可以将问号替换为空：

选择REPLACE( convert (VARCHAR(20)，PostalCode)，'?'，‘’)；

• 更重要的是，您应该将[PostalCode]字段转换为VARCHAR，这样它就不会存储这些字符。没有一个国家使用ASCII字符集没有表示的字母，并且这些字母对于VARCHAR数据类型无效，至少就我所读到的而言是这样(请参阅底部的参考资料)。实际上，允许使用的是相当小的ASCII子集，这意味着您可以很容易地在中进行过滤(或者在插入或更新时执行与上面所示相同的REPLACE )：

ALTER TABLE table ALTER COLUMN PostalCode VARCHAR(20) NOT？空；

确保检查列的当前NULL / NOT NULL设置，并使其在上面的ALTER语句中保持相同，否则可能会更改，因为默认值为NULL如果不是specified.

• If，则不能更改表的模式，并且需要定期“清理”坏数据，您可以运行以下命令：

;WITH cte AS ( SELECT * FROM TableName WHERE PostalCode <> CONVERT(NVARCHAR(50)，CONVERT(VARCHAR(50)，PostalCode) UPDATE cte SET cte.PostalCode = REPLACE(CONVERT(VARCHAR(50)，PostalCode)，'?'，'')；

请记住，如果表有数百万行，则上面的查询不会有效地工作。在这一点上，需要通过循环在较小的集合中进行处理。

作为参考，这里是维基百科关于Postal code的文章，该文章目前指出，唯一使用过的字符是：

国际标准化组织基本拉丁文alphabet

• Spaces，

中的阿拉伯数字"0“至"9"

• Letters

关于字段的最大大小，这是维基百科的List of postal codes

Answer 2

只要"?“在真实的PostalCode值中是不允许的，您可以先强制转换，然后使用REPLACE删除这些字符，将它们替换为空字符串：

replace(cast((PostalCode) as varchar))), '?', '')

警告

这些'?'字符表示原始ASCII值中未转换为等效nvarchar varchar字符的Unicode字符。这意味着这个方法将静默地移除任何这样的字符。你说你想简单地删除这些字符，但你可能想重新考虑一下。

举个例子，如果邮政编码可以包含字母，并且有人不小心键入了带重音的字母：

1234-阿BCD

最终结果将是：

1234-BCD

Answer 3

不，Unicode就是Unicode。该标准允许ASCII之外的无数“特殊”字符。最好的做法是在转换之前搜索所需的字符子集，并将它们转换为您最喜欢的ASCII代用字符。