ClickHouse 字符串搜索函数

字符串搜索函数

本节中的所有函数默认进行区分大小写的搜索。通常会提供单独的函数变体以支持不区分大小写的搜索。

注意事项

• 不区分大小写的搜索遵循英语的大小写规则。例如，英语中大写的 i 是 I，而在土耳其语中是 İ，因此对于非英语语言，结果可能不符合预期。

• 本节中的函数假设被搜索的字符串（称为 haystack）和搜索的子字符串（称为 needle）是单字节编码的文本。如果违反此假设，不会抛出异常，结果也是未定义的。对于 UTF-8 编码字符串的搜索，通常会提供单独的函数变体。同样，如果使用 UTF-8 函数变体而输入字符串不是 UTF-8 编码文本，也不会抛出异常，结果同样是未定义的。

• 注意，不会自动进行 Unicode 标准化，但你可以使用 normalizeUTF8*() 函数来实现。

---

1. position

返回子字符串 needle 在字符串 haystack 中的位置（以字节为单位，从 1 开始）。

语法：

position(haystack, needle[, start_pos])

别名：

• position(needle IN haystack)

参数：

• haystack：被搜索的字符串。类型为字符串或枚举。

• needle：要搜索的子字符串。类型为字符串。

• start_pos：在 haystack 中开始搜索的位置（从 1 开始）。类型为无符号整数。可选参数。

返回值：

• 如果找到子字符串，则返回其在 haystack 中的起始位置（以字节为单位，从 1 开始）。类型为 UInt64。

• 如果未找到子字符串，则返回 0。

如果子字符串 needle 为空，则适用以下规则：

• 如果未指定 start_pos，则返回 1。

• 如果 start_pos = 0，则返回 1。

• 如果 start_pos >= 1 且 start_pos <= length(haystack) + 1，则返回 start_pos。

• 否则返回 0。

这些规则同样适用于 locate、positionCaseInsensitive、positionUTF8 和 positionCaseInsensitiveUTF8 函数。

---

2. locate

与 position 类似，但参数顺序相反（haystack 和 needle 互换）。

从 ClickHouse v24.3 开始，locate 成为一个独立函数（以更好地兼容 MySQL），接受参数 (needle, haystack[, start_pos])。如果需要恢复旧版本的行为，可以通过设置 function_locate_has_mysql_compatible_argument_order = false 来实现。

语法：

locate(needle, haystack[, start_pos])

---

3. positionCaseInsensitive

position 的不区分大小写版本。

示例：

SELECT positionCaseInsensitive('Hello, world!', 'hello');

结果：

1

---

4. positionUTF8

与 position 类似，但假设 haystack 和 needle 是 UTF-8 编码的字符串。

示例：

SELECT positionUTF8('Motörhead', 'r');

结果：

5

---

5. positionCaseInsensitiveUTF8

与 positionUTF8 类似，但不区分大小写。

---

6. multiSearchAllPositions

与 position 类似，但返回一个数组，包含 haystack 中多个 needle 子字符串的位置（以字节为单位，从 1 开始）。

注意：

• 所有 multiSearch*() 函数最多支持 28 个 needle。

语法：

multiSearchAllPositions(haystack, [needle1, needle2, ..., needleN])

示例：

SELECT multiSearchAllPositions('Hello, World!', ['hello', '!', 'world']);

结果：

[0, 13, 0]

---

7. multiSearchAllPositionsCaseInsensitive

与 multiSearchAllPositions 类似，但不区分大小写。

---

8. multiSearchAllPositionsUTF8

与 multiSearchAllPositions 类似，但假设 haystack 和 needle 是 UTF-8 编码的字符串。

---

9. multiSearchAllPositionsCaseInsensitiveUTF8

与 multiSearchAllPositionsUTF8 类似，但不区分大小写。

---

10. multiSearchFirstPosition

与 position 类似，但返回 haystack 中匹配任意一个 needle 的最左偏移量。

语法：

multiSearchFirstPosition(haystack, [needle1, needle2, ..., needleN])

示例：

SELECT multiSearchFirstPosition('Hello World', ['llo', 'Wor', 'ld']);

---

11. multiSearchFirstPositionCaseInsensitive

与 multiSearchFirstPosition 类似，但不区分大小写。

---

12. multiSearchFirstPositionUTF8

与 multiSearchFirstPosition 类似，但假设 haystack 和 needle 是 UTF-8 编码的字符串。

---

13. multiSearchFirstPositionCaseInsensitiveUTF8

与 multiSearchFirstPositionUTF8 类似，但不区分大小写。

---

14. multiSearchFirstIndex

返回 haystack 中找到的最左 needle 的索引（从 1 开始），如果没有找到则返回 0。

语法：

multiSearchFirstIndex(haystack, [needle1, needle2, ..., needleN])

---

15. multiSearchFirstIndexCaseInsensitive

与 multiSearchFirstIndex 类似，但不区分大小写。

---

16. multiSearchFirstIndexUTF8

与 multiSearchFirstIndex 类似，但假设 haystack 和 needle 是 UTF-8 编码的字符串。

---

17. multiSearchFirstIndexCaseInsensitiveUTF8

与 multiSearchFirstIndexUTF8 类似，但不区分大小写。

---

18. multiSearchAny

如果 haystack 中至少匹配一个 needle，则返回 1，否则返回 0。

语法：

multiSearchAny(haystack, [needle1, needle2, ..., needleN])

---

19. multiSearchAnyCaseInsensitive

与 multiSearchAny 类似，但不区分大小写。

---

20. multiSearchAnyUTF8

与 multiSearchAny 类似，但假设 haystack 和 needle 是 UTF-8 编码的字符串。

---

21. multiSearchAnyCaseInsensitiveUTF8

与 multiSearchAnyUTF8 类似，但不区分大小写。

---

22. match

判断字符串 haystack 是否匹配正则表达式 pattern（使用 re2 语法）。

语法：

match(haystack, pattern)

别名：

• haystack REGEXP pattern

---

23. multiMatchAny

与 match 类似，但如果有任何一个模式匹配，则返回 1，否则返回 0。

---

24. multiMatchAnyIndex

与 multiMatchAny 类似，但返回匹配的任意索引。

---

25. multiMatchAllIndices

与 multiMatchAny 类似，但返回所有匹配的索引数组。

---

26. multiFuzzyMatchAny

与 multiMatchAny 类似，但返回 1 如果任意模式在常量编辑距离内匹配 haystack。

---

27. multiFuzzyMatchAnyIndex

与 multiFuzzyMatchAny 类似，但返回匹配的任意索引。

---

28. multiFuzzyMatchAllIndices

与 multiFuzzyMatchAny 类似，但返回所有匹配的索引数组。

---

29. extract

返回正则表达式在字符串中的第一个匹配项。

语法：

extract(haystack, pattern)

示例：

SELECT extract('number: 1, number: 2, number: 3', '\\d+') AS result;

结果：

1

---

30. extractAll

返回正则表达式在字符串中的所有匹配项。

语法：

extractAll(haystack, pattern)

示例：

SELECT extractAll('number: 1, number: 2, number: 3', '\\d+') AS result;

结果：

['1', '2', '3']

---

31. extractAllGroupsHorizontal

使用正则表达式匹配 haystack 的所有组，并返回一个数组，其中第一个数组包含所有匹配第一组的片段，第二个数组包含匹配第二组的片段，依此类推。

语法：

extractAllGroupsHorizontal(haystack, pattern)

示例：

SELECT extractAllGroupsHorizontal('abc=111, def=222, ghi=333', '("[^"]+"|\\w+)=("[^"]+"|\\w+)');

结果：

[['abc', 'def', 'ghi'], ['111', '222', '333']]

---

32. extractGroups

使用正则表达式匹配 haystack 的所有组，并返回一个数组。

语法：

extractGroups(haystack, pattern)

示例：

SELECT extractGroups('hello abc=111 world', '("[^"]+"|\\w+)=("[^"]+"|\\w+)') AS result;

结果：

['abc', '111']

---

33. extractAllGroupsVertical

使用正则表达式匹配 haystack 的所有组，并返回一个数组，其中每个数组包含每个组的匹配片段，按 haystack 中的出现顺序分组。

语法：

extractAllGroupsVertical(haystack, pattern)

示例：

SELECT extractAllGroupsVertical('abc=111, def=222, ghi=333', '("[^"]+"|\\w+)=("[^"]+"|\\w+)');

结果：

[['abc', '111'], ['def', '222'], ['ghi', '333']]

---

34. like

判断字符串 haystack 是否匹配 LIKE 表达式 pattern。

LIKE 表达式可以包含普通字符以及以下元字符：

• % 表示任意数量的任意字符（包括零个字符）。

• _ 表示单个任意字符。

• \ 用于转义 %、_ 和 \。

匹配基于 UTF-8，例如 _ 可以匹配 UTF-8 中用两个字节表示的 Unicode 码点（如 ¥）。

如果 haystack 或 LIKE 表达式不是有效的 UTF-8，行为是未定义的。

语法：

haystack LIKE pattern

---

35. notLike

与 like 类似，但返回相反的结果。

语法：

haystack NOT LIKE pattern

---

36. ilike

与 like 类似，但不区分大小写。

语法：

haystack ILIKE pattern

---

37. notILike

与 ilike 类似，但返回相反的结果。

语法：

haystack NOT ILIKE pattern

---

38. ngramDistance

计算两个字符串 haystack 和 needle 之间的 4-gram 距离。它通过计算两个 4-gram 多集的对称差并将其标准化为它们的基数之和来实现。返回值是一个介于 0 和 1 之间的 Float32，值越小，两个字符串越相似。

语法：

ngramDistance(haystack, needle)

示例：

SELECT ngramDistance('ClickHouse', 'ClickHouse!');

---

39. ngramDistanceCaseInsensitive

ngramDistance 的不区分大小写版本。

---

40. ngramDistanceUTF8

ngramDistance 的 UTF-8 版本，假设 haystack 和 needle 是 UTF-8 编码的字符串。

---

41. ngramDistanceCaseInsensitiveUTF8

ngramDistanceUTF8 的不区分大小写版本。

---

42. ngramSearch

与 ngramDistance 类似，但计算 needle 和 haystack 之间的非对称差，即 needle 中的 n-gram 数量减去公共 n-gram 的数量，然后除以 needle 中 n-gram 的数量。返回值是一个介于 0 和 1 之间的 Float32，值越大，needle 在 haystack 中的可能性越高。

语法：

ngramSearch(haystack, needle)

---

43. ngramSearchCaseInsensitive

ngramSearch 的不区分大小写版本。

---

44. ngramSearchUTF8

ngramSearch 的 UTF-8 版本，假设 haystack 和 needle 是 UTF-8 编码的字符串。

---

45. ngramSearchCaseInsensitiveUTF8

ngramSearchUTF8 的不区分大小写版本。

---

46. countSubstrings

返回子字符串 needle 在字符串 haystack 中出现的次数。

语法：

countSubstrings(haystack, needle[, start_pos])

---

47. countSubstringsCaseInsensitive

与 countSubstrings 类似，但不区分大小写。

---

48. countSubstringsCaseInsensitiveUTF8

与 countSubstringsCaseInsensitive 类似，但假设 haystack 是 UTF-8 编码的字符串。

---

49. countMatches

返回正则表达式 pattern 在字符串 haystack 中的匹配次数。

语法：

countMatches(haystack, pattern)

---

50. countMatchesCaseInsensitive

与 countMatches 类似，但不区分大小写。

---

51. regexpExtract

提取 haystack 中第一个匹配正则表达式 pattern 的字符串。

语法：

regexpExtract(haystack, pattern[, index])

示例：

SELECT regexpExtract('100-200', '(\\d+)-(\\d+)', 1);

结果：

100

---

52. hasSubsequence

如果 needle 是 haystack 的子序列，则返回 1，否则返回 0。子序列是指可以从给定字符串中删除零个或多个元素而无需改变剩余元素顺序的序列。

语法：

hasSubsequence(haystack, needle)

---

53. hasSubsequenceCaseInsensitive

与 hasSubsequence 类似，但不区分大小写。

---

54. hasSubsequenceUTF8

与 hasSubsequence 类似，但假设 haystack 和 needle 是 UTF-8 编码的字符串。

---

55. hasSubsequenceCaseInsensitiveUTF8

与 hasSubsequenceUTF8 类似，但不区分大小写。

---

56. hasToken

如果给定的 token 存在于 haystack 中，则返回 1，否则返回 0。

语法：

hasToken(haystack, token)

---

57. hasTokenOrNull

如果给定的 token 存在于 haystack 中，则返回 1；如果不存在，则返回 0；如果 token 格式错误，则返回 null。

语法：

hasTokenOrNull(haystack, token)

---

58. hasTokenCaseInsensitive

与 hasToken 类似，但不区分大小写。

---

59. hasTokenCaseInsensitiveOrNull

与 hasTokenCaseInsensitive 类似，但不区分大小写，并且如果 token 格式错误，则返回 null。