Unicode 到 Non-Unicode 的转换

Question

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

我的代码是：

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

结果是：

PostalCode       |   val
053000           | 053000?

在这里，我对结果?。有没有办法删除这些特殊字符？

Answer 1

这里有

几点需要注意：

1. 如果你想确切地看到哪个字符在那里，你可以将值转换为VARBINARY这将给你字符串中所有字符的十六进制/二进制值，并且十六进制中没有"隐藏"字符的概念：

   DECLARE @PostalCode NVARCHAR(20);
   SET @PostalCode = N'053000'+ NCHAR(0x2008); -- 0x2008 = "Punctuation Space"
   SELECT @PostalCode AS [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小端序（即UTF-16LE），字节的顺序相反。翻转最后 2 个字节 - 08和20 - 我们得到"2008"，这是我们通过 NCHAR(0x2008) 添加的"标点空格"。

   另外，请注意，RTRIM在这里根本没有帮助。

2. 简单地说，您可以用任何内容替换问号：

   SELECT REPLACE(CONVERT(VARCHAR(20), [PostalCode]), '?', '');
   

3. 更重要的是，您应该将[PostalCode]字段转换为VARCHAR，以便它不会存储这些字符。没有一个国家使用未在 ASCII 字符集中表示且对 VARCHAR 数据类型无效的字母，至少就我所读到的而言（有关参考资料，请参阅底部部分）。事实上，允许的是 ASCII 的一个相当小的子集，这意味着您可以轻松地过滤（或者只是在插入或更新时执行如上所示的相同REPLACE）：

   ALTER TABLE [table] ALTER COLUMN [PostalCode] VARCHAR(20) [NOT]? NULL;
   

   请务必检查列的当前 NULL/NOT NULL 设置，并在上面的 ALTER 语句中使其相同，否则如果未指定，则可能会更改它，因为默认值NULL。

4. 如果无法更改表的架构，并且需要定期"清理"错误数据，则可以运行以下命令：

   ;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]), '?', '');
   

   请记住，如果表有数百万行，则上述查询不能有效地工作。此时，它需要通过循环在较小的集合中处理。

---

作为参考，这里是邮政编码的维基百科文章，目前指出唯一使用的字符是：

• 阿拉伯数字"0"到"9"
• ISO基本拉丁字母的字母
• 空格、连字符

关于字段的最大大小，这里是维基百科邮政编码列表

Answer 2

只要

真正的邮政编码值中不允许使用"？"，就可以先强制转换，然后使用REPLACE删除这些字符，将它们替换为空字符串：

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

警告

这些'?'字符表示原始 nvarchar 值中未转换为等效 ASCII varchar字符的 Unicode 字符。这意味着此方法将以静默方式删除任何此类字符。您说您想简单地删除这些字符，但您可能需要重新考虑。

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

1234-ÁBCD

最终结果将是：

1234-BCD

Answer 3

不，Unicode 就是 Unicode。 该标准允许在ASCII之外使用无数"特殊"字符。 您能做的最好的事情是在转换之前搜索所需的字符子集，并将它们转换为您喜欢的 ASCII 替身。

Answer 4

如果只想删除最后一个特殊字符，而使其余字符保持不变，则可以尝试以下方法之一在转换为varchar之前将其删除：

里普里

姆

也许最后一个特殊字符被认为是一个空格。尝试 RTRIM：

cast(rtrim(PostalCode) as varchar))

识别和删除

首先，使用 RIGHT 和 UNICODE 从表的示例行中找到最后一个字符的 Unicode 代码点：

select unicode(right(PostalCode, 1)) from …

这应该给你一个整数。

接下来，在不需要时从行中删除该字符。我们可以用 NCHAR 将该整数转换回 unicode 字符：

select (case when right(PostalCode, 1) = nchar(the_number_from_above)
        then left(PostalCode, len(PostalCode) - 1)
        else PostalCode end)