unicode - 如何在标准 SQL 中使用 Unicode 规范化删除变音符号(例如重音符号)？

Question

我们如何使用新的 normalize 从 BigQuery 中的字符串中删除变音符号功能如:

café

造成:

cafe

Answer 1

简短的回答

在您了解 normalize 的作用之后，这实际上非常简单:

WITH data AS(
  SELECT 'Ãâíüçãõ' AS text
)

SELECT
  REGEXP_REPLACE(NORMALIZE(text, NFD), r'\pM', '') nfd_result,
  REGEXP_REPLACE(NORMALIZE(text, NFKD), r'\pM', '') nfkd_result
FROM data

结果:

Row   nfd_result    nfkd_result  
1     Aaiucao       Aaiucao  

您可以使用选项“NFD”或“NFKD”，并且在大多数情况下，它应该可以工作(您仍然应该了解这两个选项之间的差异以更好地处理您的数据)。

更完整的答案

基本上 normalize 所做的是将字符串中的所有 unicode 转换为其规范的等效形式(或兼容形式)，以便我们有等效的比较引用(现在理解这一点已经需要了解一些概念)。

重点是，unicode 不仅建立了数字(它们的代码点由 U+ 表示)和它们的字形之间的映射，而且还建立了这些点之间如何相互作用的一些规则。

例如，让我们取字形 á .

对于这个字符，我们不只有一个 unicode。我们实际上可以将它表示为 U+00E1或喜欢 U+0061U+0301这是 a 的 unicodes和 ´ .

是的! Unicode 的定义方式使您可以组合字符和变音符号，并通过一个接一个地排序来表示它们的联合。

事实上，你可以玩弄 combining diacritics在 Unicode 中使用 online conversor :



Unicode 将这些可以组合成变音符号的字符类型定义为 precomposed characters通过使用一个聪明而简单的想法:未预先组合的字符具有所谓的 0(零)组合类；可以组合的点接收一个正组合类(例如， ´ 具有类 230)，用于断言应如何表示最终字形。

这很酷，但最终产生了一个问题，它解释了我们从一开始就讨论的函数 normalize:如果我们读取两个字符串，一个是 unicode U+0061U+0301和其他与 U+00E1 (两者都是 á )，它们应该被认为是等效的!事实上，它是以不同方式表示的同一个字形。

这正是 normalize是在做。 Unicode 为每个字符定义了一种规范形式，因此，在规范化时，最终结果应该是这样的:如果我们有两个具有相同字形的不同代码点的字符串，我们仍然可以将两者视为相等。

好吧，我们如何规范代码点基本上有两种主要的可能性:要么将不同的 unicode 组合成一个(在我们的例子中，这将把 U+0061U+0301 转换成 U+00E1)，或者我们可以分解(这将是另一种方式周围，​​将 U+00E1 转换为 U+0061U+0301 )。

在这里你可以更清楚地看到它:



NF 表示 canonical equivalent . NFC 表示检索规范复合字符(统一)； NFD则相反，分解字符。

您可以使用此信息在 BigQuery 中进行操作:

WITH data AS(
  SELECT 'Amélie' AS text
)

SELECT
  text,
  TO_CODE_POINTS(NORMALIZE(text, NFC)) nfc_result,
  TO_CODE_POINTS(NORMALIZE(text, NFD)) nfd_result
FROM data

结果:



注意 nfd列还有一个代码点。现在你已经知道那是什么了: ´与 e 分开.

如果您阅读 BigQuery 的 normalize 文档，您会发现它也支持 NFKC 和 NFKD 类型。这种类型(带有字母 K)不是通过规范等价来规范化的，而是通过“兼容性”来规范化的，也就是说，它也将一些字符分解为其组成字母，而不仅仅是变音符号:



来信 ffi (这与 ffi 不同。这种类型的字符被称为 ligature )也被构成它的字母分解(因此等价性丢失，因为对于某些应用程序，ffi 可能与 ffi 不同，因此名称兼容表)。

现在我们知道如何将字符分解成主要字形，然后是变音符号，我们可以使用 regex只匹配它们以从字符串中删除(这是由表达式 \pM 完成的，它只匹配变音符号):

WITH data AS(
  SELECT 'café' AS text
)

SELECT
  REGEXP_REPLACE(NORMALIZE(text, NFD), r'\pM', '') nfd_result
FROM data

这就是(希望如此)归一化函数以及它如何用于删除变音符号的全部内容。感谢用户 sigpwned，我找到了所有这些信息以及他对 this question 的回答.当我尝试它但它并没有完全奏效时，我决定研究这些方法背后的一些理论并想把它写下来:)。希望它对更多人有用，因为它绝对对我有用。