跳到主要内容
跳到主要内容

字符串搜索函数

本节中的所有函数默认为区分大小写搜索。区分大小写的搜索通常由单独的函数变体提供。

备注

不区分大小写的搜索遵循英语的大小写规则。例如，在英语中大写的 i 是 I，而在土耳其语中是 İ - 非英语语言的结果可能会意外。

本节中的函数还假设被搜索的字符串（在本节中称为 haystack）和搜索字符串（在本节中称为 needle）为单字节编码文本。如果违反此假设，则不会抛出异常，结果是未定义的。使用 UTF-8 编码字符串的搜索通常由单独的函数变体提供。同样，如果使用了 UTF-8 函数变体且输入字符串不是 UTF-8 编码文本，则不会抛出异常，结果是未定义的。请注意，没有进行自动 Unicode 规范化，但您可以使用 normalizeUTF8*() 函数来实现这一点。

通用字符串函数 和 字符串替换函数 被单独描述。

position​

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

语法

position(haystack, needle[, start_pos])

别名：

  • position(needle IN haystack)

参数

  • haystack — 执行搜索的字符串。 String 或 Enum。
  • needle — 要搜索的子字符串。 String。
  • start_pos – 在 haystack 中开始搜索的位置（基于 1）。 UInt。 可选。

返回值

  • 如果找到子字符串，则返回以字节为单位且从 1 开始的起始位置。 UInt64。
  • 如果未找到子字符串，则返回 0。 UInt64。

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

  • 如果未指定 start_pos：返回 1
  • 如果 start_pos = 0：返回 1
  • 如果 start_pos >= 1 且 start_pos <= length(haystack) + 1：返回 start_pos
  • 否则：返回 0

相同规则也适用于函数 locate、positionCaseInsensitive、positionUTF8 和 positionCaseInsensitiveUTF8。

示例

查询：

SELECT position('Hello, world!', '!');

结果：

┌─position('Hello, world!', '!')─┐
│                             13 │
└────────────────────────────────┘

带有 start_pos 参数的示例：

查询：

SELECT
    position('Hello, world!', 'o', 1),
    position('Hello, world!', 'o', 7)

结果：

┌─position('Hello, world!', 'o', 1)─┬─position('Hello, world!', 'o', 7)─┐
│                                 5 │                                 9 │
└───────────────────────────────────┴───────────────────────────────────┘

needle IN haystack 语法的示例：

查询：

SELECT 6 = position('/' IN s) FROM (SELECT 'Hello/World' AS s);

结果：

┌─equals(6, position(s, '/'))─┐
│                           1 │
└─────────────────────────────┘

带有空 needle 子字符串的示例：

查询：

SELECT
    position('abc', ''),
    position('abc', '', 0),
    position('abc', '', 1),
    position('abc', '', 2),
    position('abc', '', 3),
    position('abc', '', 4),
    position('abc', '', 5)

结果：

┌─position('abc', '')─┬─position('abc', '', 0)─┬─position('abc', '', 1)─┬─position('abc', '', 2)─┬─position('abc', '', 3)─┬─position('abc', '', 4)─┬─position('abc', '', 5)─┐
│                   1 │                      1 │                      1 │                      2 │                      3 │                      4 │                      0 │
└─────────────────────┴────────────────────────┴────────────────────────┴────────────────────────┴────────────────────────┴────────────────────────┴────────────────────────┘

locate​

类似于 position，但参数 haystack 和 locate 交换位置。

此函数的行为取决于 ClickHouse 的版本：

  • 在版本 < v24.3 中，locate 是函数 position 的别名，接受参数 (haystack, needle[, start_pos])。
  • 在版本 >= 24.3 中，locate 是一个独立的函数（为了更好地与 MySQL 兼容），接受参数 (needle, haystack[, start_pos])。可以使用设置 function_locate_has_mysql_compatible_argument_order = false 恢复之前的行为。

语法

locate(needle, haystack[, start_pos])

positionCaseInsensitive​

Position 的不区分大小写版本。

示例

查询：

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

结果：

┌─positionCaseInsensitive('Hello, world!', 'hello')─┐
│                                                 1 │
└───────────────────────────────────────────────────┘

positionUTF8​

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

示例

函数 positionUTF8 正确将字符 ö（由两个点表示）计为一个 Unicode 代码点：

查询：

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

结果：

┌─position('Motörhead', 'r')─┐
│                          5 │
└────────────────────────────┘

positionCaseInsensitiveUTF8​

类似于 positionUTF8，但不区分大小写搜索。

multiSearchAllPositions​

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

备注

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

语法

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

参数

  • haystack — 执行搜索的字符串。 String。
  • needle — 要搜索的子字符串。 Array。

返回值

  • 如果找到子字符串，则返回以字节为单位且从 1 开始的起始位置的数组。
  • 如果未找到子字符串，则返回 0。

示例

查询：

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

结果：

┌─multiSearchAllPositions('Hello, World!', ['hello', '!', 'world'])─┐
│ [0,13,0]                                                          │
└───────────────────────────────────────────────────────────────────┘

multiSearchAllPositionsCaseInsensitive​

类似于 multiSearchAllPositions，但忽略大小写。

语法

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

参数

  • haystack — 执行搜索的字符串。 String。
  • needle — 要搜索的子字符串。 Array。

返回值

  • 如果找到子字符串，则返回以字节为单位且从 1 开始的起始位置的数组。
  • 如果未找到子字符串，则返回 0。

示例

查询：

SELECT multiSearchAllPositionsCaseInsensitive('ClickHouse',['c','h']);

结果：

["1","6"]

multiSearchAllPositionsUTF8​

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

语法

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

参数

  • haystack — UTF-8 编码的字符串，在其中执行搜索。 String。
  • needle — UTF-8 编码的子字符串。 Array。

返回值

  • 如果找到子字符串，则返回以字节为单位且从 1 开始的起始位置的数组。
  • 如果未找到子字符串，则返回 0。

示例

给定 ClickHouse 作为 UTF-8 字符串，查找 C（\x43）和 H（\x48）的位置。

查询：

SELECT multiSearchAllPositionsUTF8('\x43\x6c\x69\x63\x6b\x48\x6f\x75\x73\x65',['\x43','\x48']);

结果：

["1","6"]

multiSearchAllPositionsCaseInsensitiveUTF8​

类似于 multiSearchAllPositionsUTF8，但忽略大小写。

语法

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

参数

  • haystack — UTF-8 编码的字符串，在其中执行搜索。 String。
  • needle — UTF-8 编码的子字符串。 Array。

返回值

  • 如果找到子字符串，则返回以字节为单位且从 1 开始的起始位置的数组。
  • 如果未找到子字符串，则返回 0。

示例

给定 ClickHouse 作为 UTF-8 字符串，查找 c（\x63）和 h（\x68）的位置。

查询：

SELECT multiSearchAllPositionsCaseInsensitiveUTF8('\x43\x6c\x69\x63\x6b\x48\x6f\x75\x73\x65',['\x63','\x68']);

结果：

["1","6"]

multiSearchFirstPosition​

类似于 position，但返回在 haystack 字符串中匹配任意多个 needle 字符串的最左偏移量。

函数 multiSearchFirstPositionCaseInsensitive、multiSearchFirstPositionUTF8 和 multiSearchFirstPositionCaseInsensitiveUTF8 提供了此函数的不区分大小写和/或 UTF-8 变体。

语法

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

参数

  • haystack — 执行搜索的字符串。 String。
  • needle — 要搜索的子字符串。 Array。

返回值

  • 在 haystack 字符串中匹配任意多个 needle 字符串的最左偏移量。
  • 如果没有匹配，则返回 0。

示例

查询：

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

结果：

3

multiSearchFirstPositionCaseInsensitive​

类似于 multiSearchFirstPosition，但忽略大小写。

语法

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

参数

  • haystack — 执行搜索的字符串。 String。
  • needle — 要搜索的子字符串。 Array。

返回值

  • 在 haystack 字符串中匹配任意多个 needle 字符串的最左偏移量。
  • 如果没有匹配，则返回 0。

示例

查询：

SELECT multiSearchFirstPositionCaseInsensitive('HELLO WORLD',['wor', 'ld', 'ello']);

结果：

2

multiSearchFirstPositionUTF8​

类似于 multiSearchFirstPosition，但假定 haystack 和 needle 是 UTF-8 字符串。

语法

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

参数

  • haystack — UTF-8 字符串，在其中执行搜索。 String。
  • needle — 要搜索的 UTF-8 子字符串。 Array。

返回值

  • 在 haystack 字符串中匹配任意多个 needle 字符串的最左偏移量。
  • 如果没有匹配，则返回 0。

示例

在 UTF-8 字符串 hello world 中，查找任意给定 needle 的最左偏移量。

查询：

SELECT multiSearchFirstPositionUTF8('\x68\x65\x6c\x6c\x6f\x20\x77\x6f\x72\x6c\x64',['wor', 'ld', 'ello']);

结果：

2

multiSearchFirstPositionCaseInsensitiveUTF8​

类似于 multiSearchFirstPosition，但假定 haystack 和 needle 是 UTF-8 字符串并忽略大小写。

语法

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

参数

  • haystack — UTF-8 字符串，在其中执行搜索。 String。
  • needle — 要搜索的 UTF-8 子字符串。 Array。

返回值

  • 在 haystack 字符串中匹配任意多个 needle 字符串的最左偏移量，忽略大小写。
  • 如果没有匹配，则返回 0。

示例

在 UTF-8 字符串 HELLO WORLD 中，查找任意给定 needle 的最左偏移量。

查询：

SELECT multiSearchFirstPositionCaseInsensitiveUTF8('\x48\x45\x4c\x4c\x4f\x20\x57\x4f\x52\x4c\x44',['wor', 'ld', 'ello']);

结果：

2

multiSearchFirstIndex​

返回字符串 haystack 中找到的最左 needlei 的索引 i（从 1 开始），否则返回 0。

函数 multiSearchFirstIndexCaseInsensitive、multiSearchFirstIndexUTF8 和 multiSearchFirstIndexCaseInsensitiveUTF8 提供了不区分大小写和/或 UTF-8 的变体。

语法

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

参数

  • haystack — 执行搜索的字符串。 String。
  • needle — 要搜索的子字符串。 Array。

返回值

  • 找到的最左 needle 的索引（从 1 开始）。否则，如果没有匹配，则返回 0。 UInt8。

示例

查询：

SELECT multiSearchFirstIndex('Hello World',['World','Hello']);

结果：

1

multiSearchFirstIndexCaseInsensitive​

返回字符串 haystack 中找到的最左 needlei 的索引 i（从 1 开始），否则返回 0。忽略大小写。

语法

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

参数

  • haystack — 执行搜索的字符串。 String。
  • needle — 要搜索的子字符串。 Array。

返回值

  • 找到的最左 needle 的索引（从 1 开始）。否则，如果没有匹配，则返回 0。 UInt8。

示例

查询：

SELECT multiSearchFirstIndexCaseInsensitive('hElLo WoRlD',['World','Hello']);

结果：

1

multiSearchFirstIndexUTF8​

返回字符串 haystack 中找到的最左 needlei 的索引 i（从 1 开始），否则返回 0。假定 haystack 和 needle 是 UTF-8 编码的字符串。

语法

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

参数

  • haystack — UTF-8 字符串，在其中执行搜索。 String。
  • needle — 要搜索的 UTF-8 字符串数组。 Array

返回值

  • 找到的最左 needle 的索引（从 1 开始）。否则，如果没有匹配，则返回 0。 UInt8。

示例

给定 Hello World 作为 UTF-8 字符串，查找 UTF-8 字符串 Hello 和 World 的第一个索引。

查询：

SELECT multiSearchFirstIndexUTF8('\x48\x65\x6c\x6c\x6f\x20\x57\x6f\x72\x6c\x64',['\x57\x6f\x72\x6c\x64','\x48\x65\x6c\x6c\x6f']);

结果：

1

multiSearchFirstIndexCaseInsensitiveUTF8​

返回字符串 haystack 中找到的最左 needlei 的索引 i（从 1 开始），否则返回 0。假定 haystack 和 needle 是 UTF-8 编码的字符串。忽略大小写。

语法

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

参数

  • haystack — UTF-8 字符串，在其中执行搜索。 String。
  • needle — 要搜索的 UTF-8 字符串数组。 Array。

返回值

  • 找到的最左 needle 的索引（从 1 开始）。否则，如果没有匹配，则返回 0。 UInt8。

示例

给定 HELLO WORLD 作为 UTF-8 字符串，查找 UTF-8 字符串 hello 和 world 的第一个索引。

查询：

SELECT multiSearchFirstIndexCaseInsensitiveUTF8('\x48\x45\x4c\x4c\x4f\x20\x57\x4f\x52\x4c\x44',['\x68\x65\x6c\x6c\x6f','\x77\x6f\x72\x6c\x64']);

结果：

1

multiSearchAny​

如果至少有一个字符串 needlei 与字符串 haystack 匹配，则返回 1，否则返回 0。

函数 multiSearchAnyCaseInsensitive、multiSearchAnyUTF8 和 multiSearchAnyCaseInsensitiveUTF8 提供了不区分大小写和/或 UTF-8 的变体。

语法

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

参数

  • haystack — 执行搜索的字符串。 String。
  • needle — 要搜索的子字符串。 Array。

返回值

  • 如果至少有一个匹配，则返回 1。
  • 如果没有匹配，则返回 0。

示例

查询：

SELECT multiSearchAny('ClickHouse',['C','H']);

结果：

1

multiSearchAnyCaseInsensitive​

类似于 multiSearchAny，但忽略大小写。

语法

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

参数

  • haystack — 执行搜索的字符串。 String。
  • needle — 要搜索的子字符串。 Array

返回值

  • 如果至少有一个不区分大小写的匹配，则返回 1。
  • 如果没有至少一个不区分大小写的匹配，则返回 0。

示例

查询：

SELECT multiSearchAnyCaseInsensitive('ClickHouse',['c','h']);

结果：

1

multiSearchAnyUTF8​

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

语法

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

参数

  • haystack — UTF-8 字符串，在其中执行搜索。 String。
  • needle — 要搜索的 UTF-8 子字符串。 Array。

返回值

  • 如果至少有一个匹配，则返回 1。
  • 如果没有至少一个匹配，则返回 0。

示例

给定 ClickHouse 作为 UTF-8 字符串，检查单词中是否有字母 C（\x43）或 H（\x48）。

查询：

SELECT multiSearchAnyUTF8('\x43\x6c\x69\x63\x6b\x48\x6f\x75\x73\x65',['\x43','\x48']);

结果：

1

multiSearchAnyCaseInsensitiveUTF8​

类似于 multiSearchAnyUTF8，但忽略大小写。

语法

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

参数

  • haystack — UTF-8 字符串，在其中执行搜索。 String。
  • needle — 要搜索的 UTF-8 子字符串。 Array

返回值

  • 如果至少有一个不区分大小写的匹配，则返回 1。
  • 如果没有至少一个不区分大小写的匹配，则返回 0。

示例

给定 ClickHouse 作为 UTF-8 字符串，检查单词中是否有字母 h（\x68），忽略大小写。

查询：

SELECT multiSearchAnyCaseInsensitiveUTF8('\x43\x6c\x69\x63\x6b\x48\x6f\x75\x73\x65',['\x68']);

结果：

1

hasAnyTokens​

备注

此函数仅在设置 allow_experimental_full_text_index 被启用时可以使用。

如果至少有一个字符串 needlei 与 input 列匹配，则返回 1，否则返回 0。

语法

hasAnyTokens(input, ['needle1', 'needle2', ..., 'needleN'])

参数

备注

列 input 必须具有 [文本索引][../../engines/table-engines/mergetree-family/invertedindexes.md]。

input 字符串由索引定义中的分词器进行分词。

每个 needle 数组元素 tokeni 被视为一个单一的令牌，即不再进一步分词。 例如，如果您想用索引 tokenizer = ngrams(5) 搜索 ClickHouse，提供这些 needles: ['Click', 'lickH', 'ickHo', 'ckHou', 'kHous', 'House']。 要生成这些 needles，您可以使用 tokens 函数。 重复的令牌是无效的，例如，['ClickHouse', 'ClickHouse'] 与 ['ClickHouse'] 是相同的。

返回值

  • 如果至少有一个匹配，则返回 1。
  • 否则返回 0。

示例

查询：

CREATE TABLE table (
    id UInt32,
    msg String,
    INDEX idx(msg) TYPE text(tokenizer = splitByString(['()', '\\'])
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO table VALUES (1, '()a,\\bc()d'), (2, '()\\a()bc\\d'), (3, ',()a\\,bc,(),d,');

SELECT count() FROM table WHERE hasAnyTokens(msg, ['a', 'd']);

结果：

3

使用 tokens 函数生成针

查询：

SELECT count() FROM table WHERE hasAnyTokens(msg, tokens('a()d', 'splitByString', ['()', '\\']));

结果：

3

hasAllTokens​

备注

此函数仅在设置 allow_experimental_full_text_index 被启用时可以使用。

类似于 hasAnyTokens，只有在所有字符串 needlei 与 input 列匹配时才返回 1，否则返回 0。

语法

hasAllTokens(input, ['needle1', 'needle2', ..., 'needleN'])

参数

备注

列 input 必须具有 [文本索引][../../engines/table-engines/mergetree-family/invertedindexes.md]。

input 字符串由索引定义中的分词器进行分词。

每个 needle 数组元素 tokeni 被视为一个单一的令牌，即不再进一步分词。 例如，如果您想用索引 tokenizer = ngrams(5) 搜索 ClickHouse，提供这些 needles: ['Click', 'lickH', 'ickHo', 'ckHou', 'kHous', 'House']。 要生成这些 needles，您可以使用 tokens 函数。 重复的令牌是无效的，例如，['ClickHouse', 'ClickHouse'] 与 ['ClickHouse'] 是相同的。

返回值

  • 如果所有 needle 匹配，则返回 1。
  • 否则返回 0。

示例

查询：

CREATE TABLE table (
    id UInt32,
    msg String,
    INDEX idx(msg) TYPE text(tokenizer = splitByString(['()', '\\'])
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO table VALUES (1, '()a,\\bc()d'), (2, '()\\a()bc\\d'), (3, ',()a\\,bc,(),d,');

SELECT count() FROM table WHERE hasAllTokens(msg, ['a', 'd']);

结果：

1

使用 tokens 函数生成针

查询：

SELECT count() FROM table WHERE hasAllTokens(msg, tokens('a()d', 'splitByString', ['()', '\\']));

结果：

1

match​

返回字符串 haystack 是否与正则表达式 pattern 匹配，re2 正则表达式语法。

匹配是基于 UTF-8 的，例如 . 匹配 Unicode 代码点 ¥，其在 UTF-8 中使用两个字节表示。正则表达式中不得包含空字节。如果 haystack 或模式不是有效 UTF-8，则行为是未定义的。

与 re2 的默认行为不同，. 匹配换行符。要禁用此功能，请在模式前添加 (?-s)。

如果您只想在字符串中查找子字符串，可以使用函数 like 或 position，它们的速度比此函数快得多。

语法

match(haystack, pattern)

别名：haystack REGEXP pattern operator

multiMatchAny​

类似于 match，但如果至少有一个模式匹配，则返回 1，否则返回 0。

备注

multi[Fuzzy]Match*() 函数系列使用 (Vectorscan)[https://github.com/VectorCamp/vectorscan] 库。因此，它们仅在 ClickHouse 被编译为支持 vectorscan 时启用。

要关闭所有使用 hyperscan 的函数，请使用设置 SET allow_hyperscan = 0;。

由于 vectorscan 的限制，haystack 字符串的长度必须小于 232 字节。

Hyperscan 通常容易受到正则表达式拒绝服务（ReDoS）攻击（例如，请参见 (here)[https://www.usenix.org/conference/usenixsecurity22/presentation/turonova]、(here)[https://doi.org/10.1007/s10664-021-10033-1] 和 (here)[https://doi.org/10.1145/3236024.3236027]）。建议用户仔细检查提供的模式。

如果您只想在字符串中搜索多个子字符串，可以使用函数 multiSearchAny，它的速度比此函数快得多。

语法

multiMatchAny(haystack, \[pattern<sub>1</sub>, pattern<sub>2</sub>, ..., pattern<sub>n</sub>\])

multiMatchAnyIndex​

类似于 multiMatchAny，但返回与 haystack 匹配的任何索引。

语法

multiMatchAnyIndex(haystack, \[pattern<sub>1</sub>, pattern<sub>2</sub>, ..., pattern<sub>n</sub>\])

multiMatchAllIndices​

类似于 multiMatchAny，但返回所有匹配 haystack 的索引数组，顺序无关。

语法

multiMatchAllIndices(haystack, \[pattern<sub>1</sub>, pattern<sub>2</sub>, ..., pattern<sub>n</sub>\])

multiFuzzyMatchAny​

类似于 multiMatchAny，但如果任何模式在某个常量 编辑距离 内匹配 haystack，则返回 1。此函数依赖于 hyperscan 库的实验性功能，并且对于某些极端情况可能很慢。性能依赖于编辑距离值和使用的模式，但始终比非模糊变体更昂贵。

备注

multiFuzzyMatch*() 函数系列不支持 UTF-8 正则表达式（因为它将其视为字节序列），这受到 hyperscan 的限制。

语法

multiFuzzyMatchAny(haystack, distance, \[pattern<sub>1</sub>, pattern<sub>2</sub>, ..., pattern<sub>n</sub>\])

multiFuzzyMatchAnyIndex​

类似于 multiFuzzyMatchAny，但返回在常量编辑距离内与 haystack 匹配的任何索引。

语法

multiFuzzyMatchAnyIndex(haystack, distance, \[pattern<sub>1</sub>, pattern<sub>2</sub>, ..., pattern<sub>n</sub>\])

multiFuzzyMatchAllIndices​

类似于 multiFuzzyMatchAny，但返回在常量编辑距离内与 haystack 匹配的所有索引的数组，顺序无关。

语法

multiFuzzyMatchAllIndices(haystack, distance, \[pattern<sub>1</sub>, pattern<sub>2</sub>, ..., pattern<sub>n</sub>\])

extract​

返回字符串中正则表达式的第一个匹配。 如果 haystack 不匹配 pattern 正则表达式，则返回空字符串。

如果正则表达式有捕获组，函数将输入字符串与第一个捕获组匹配。

语法

extract(haystack, pattern)

参数

返回值

  • 在 haystack 字符串中正则表达式的第一个匹配。 String。

示例

查询：

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

结果：

┌─result─┐
│ 1      │
└────────┘

extractAll​

返回字符串中正则表达式的所有匹配数组。如果 haystack 不匹配 pattern 正则表达式，则返回空字符串。

与子模式的行为与函数 extract 相同。

语法

extractAll(haystack, pattern)

参数

返回值

  • 在 haystack 字符串中正则表达式的匹配数组。 Array(String)。

示例

查询：

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

结果：

┌─result────────┐
│ ['1','2','3'] │
└───────────────┘

extractAllGroupsHorizontal​

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

此函数比 extractAllGroupsVertical 速度慢。

语法

extractAllGroupsHorizontal(haystack, pattern)

参数

  • haystack — 输入字符串。 String。
  • pattern — 采用 re2 正则表达式语法 的正则表达式。必须包含组，每个组用括号括起来。如果 pattern 不包含组，则会抛出异常。 String。

返回值

  • 匹配的数组数组。 Array。
备注

如果 haystack 不匹配 pattern 正则表达式，则返回一个空数组的数组。

示例

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

结果：

┌─extractAllGroupsHorizontal('abc=111, def=222, ghi=333', '("[^"]+"|\\w+)=("[^"]+"|\\w+)')─┐
│ [['abc','def','ghi'],['111','222','333']]                                                │
└──────────────────────────────────────────────────────────────────────────────────────────┘

extractGroups​

使用给定的正则表达式匹配给定输入字符串的所有组，返回匹配的数组数组。

语法

extractGroups(haystack, pattern)

参数

  • haystack — 输入字符串。 String。
  • pattern — 采用 re2 正则表达式语法 的正则表达式。必须包含组，每个组用括号括起来。如果 pattern 不包含组，则会抛出异常。 String。

返回值

  • 匹配的数组数组。 Array。

示例

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

结果：

┌─result────────┐
│ ['abc','111'] │
└───────────────┘

extractAllGroupsVertical​

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

语法

extractAllGroupsVertical(haystack, pattern)

参数

  • haystack — 输入字符串。 String。
  • pattern — 采用 re2 正则表达式语法 的正则表达式。必须包含组，每个组用括号括起来。如果 pattern 不包含组，则会抛出异常。 String。

返回值

  • 匹配的数组数组。 Array。
备注

如果 haystack 不匹配 pattern 正则表达式，则返回一个空数组。

示例

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

结果：

┌─extractAllGroupsVertical('abc=111, def=222, ghi=333', '("[^"]+"|\\w+)=("[^"]+"|\\w+)')─┐
│ [['abc','111'],['def','222'],['ghi','333']]                                            │
└────────────────────────────────────────────────────────────────────────────────────────┘

like​

返回字符串 haystack 是否匹配 LIKE 表达式 pattern。

LIKE 表达式可以包含正常字符和以下元符号：

  • % 表示任意数量的任意字符（包括零个字符）。
  • _ 表示单个任意字符。
  • \ 用于转义字面量 %、_ 和 \。

匹配是基于 UTF-8 的，例如 _ 匹配 Unicode 代码点 ¥，其在 UTF-8 中使用两个字节表示。

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

没有进行自动 Unicode 规范化，您可以使用 normalizeUTF8*() 函数来实现这一点。

要匹配字面 %、_ 和 \（即 LIKE 元字符），请在它们前面加上一个反斜杠：\%、\_ 和 \\。 如果反斜杠前附加的字符不是 %、_ 或 \\，则反斜杠失去其特殊含义（即被字面解释）。 请注意，ClickHouse 还需要将字符串中的反斜杠 进行转义，因此您实际上需要写 \\%、\\_ 和 \\\\。

对于 %needle% 形式的 LIKE 表达式，该函数的速度与 position 函数相同。 所有其他 LIKE 表达式内部转换为正则表达式，并以与 match 函数相似的性能执行。

语法

like(haystack, pattern)

别名：haystack LIKE pattern (操作符)

notLike​

类似于 like，但否定结果。

别名：haystack NOT LIKE pattern (操作符)

ilike​

类似于 like，但不区分大小写进行搜索。

别名：haystack ILIKE pattern (操作符)

notILike​

类似于 ilike，但否定结果。

别名：haystack NOT ILIKE pattern (操作符)

ngramDistance​

计算 haystack 字符串和 needle 字符串之间的 4-gram 距离。为此，它计算两个 4-grams 的对称差，并通过它们的基数总和进行归一化。返回一个 Float32，范围在 0 到 1 之间。结果越小，字符串之间越相似。

函数 ngramDistanceCaseInsensitive、ngramDistanceUTF8 和 ngramDistanceCaseInsensitiveUTF8 提供不区分大小写和/或 UTF-8 的变体。

语法

ngramDistance(haystack, needle)

参数

返回值

  • 0 到 1 之间的值表示两个字符串之间的相似度。 Float32

实现细节

如果常量 needle 或 haystack 参数超过 32Kb，则此函数将抛出异常。如果任何非常量 haystack 或 needle 参数超过 32Kb，则距离总是返回 1。

示例

两个字符串越相似，结果值越接近 0（完全相同）。

查询：

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

结果：

0.06666667

两个字符串越不相似，结果值越大。 查询：

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

结果：

0.5555556

ngramDistanceCaseInsensitive​

提供不区分大小写的 ngramDistance 变体。

语法

ngramDistanceCaseInsensitive(haystack, needle)

参数

返回值

  • 0 到 1 之间的值表示两个字符串之间的相似度。 Float32

示例

使用 ngramDistance 时，大小写的不同会影响相似度值：

查询：

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

结果：

0.71428573

使用 ngramDistanceCaseInsensitive 时，大小写被忽略，因此仅在大小写不同的两个相同字符串现在将返回一个低的相似度值：

查询：

SELECT ngramDistanceCaseInsensitive('ClickHouse','clickhouse');

结果：

0

ngramDistanceUTF8​

提供了一个 UTF-8 变体的 ngramDistance。假设 needle 和 haystack 字符串为 UTF-8 编码的字符串。

语法

ngramDistanceUTF8(haystack, needle)

参数

返回值

  • 表示两个字符串之间相似度的值，范围为 0 到 1。 Float32

示例

查询：

SELECT ngramDistanceUTF8('abcde','cde');

结果：

0.5

ngramDistanceCaseInsensitiveUTF8​

提供了一个不区分大小写的 ngramDistanceUTF8 变体。

语法

ngramDistanceCaseInsensitiveUTF8(haystack, needle)

参数

返回值

  • 表示两个字符串之间相似度的值，范围为 0 到 1。 Float32

示例

查询：

SELECT ngramDistanceCaseInsensitiveUTF8('abcde','CDE');

结果：

0.5

ngramSearch​

类似于 ngramDistance ，但计算 needle 字符串和 haystack 字符串之间的非对称差异，即 n-gram 的数量来自 needle 减去共同的 n-gram 数量，再根据 needle 的 n-gram 数量进行归一化。返回一个值在 0 和 1 之间的 Float32。结果越大，needle 出现在 haystack 中的可能性越大。此函数对于模糊字符串搜索很有用。也可以参考函数 soundex。

函数 ngramSearchCaseInsensitive、ngramSearchUTF8、ngramSearchCaseInsensitiveUTF8 提供了此函数的不区分大小写和/或 UTF-8 变体。

语法

ngramSearch(haystack, needle)

参数

返回值

  • 表示 needle 在 haystack 中的可能性的值，范围为 0 到 1。 Float32

实现细节

备注

UTF-8 变体使用 3-gram 距离。这些并不是完全公平的 n-gram 距离。我们使用 2 字节哈希来哈希 n-gram，然后计算这些哈希表之间的（非）对称差异 - 可能会发生哈希冲突。对于不区分大小写的 UTF-8 格式，我们不使用公平的 tolower 函数 - 我们将每个代码点字节的第 5 位（从零开始）以及如果字节大于一个的第零字节的第一位置零 - 这适用于拉丁字母和大多数西里尔字母。

示例

查询：

SELECT ngramSearch('Hello World','World Hello');

结果：

0.5

ngramSearchCaseInsensitive​

提供了一个不区分大小写的 ngramSearch 变体。

语法

ngramSearchCaseInsensitive(haystack, needle)

参数

返回值

  • 表示 needle 在 haystack 中的可能性的值，范围为 0 到 1。 Float32

结果越大，needle 出现在 haystack 中的可能性越大。

示例

查询：

SELECT ngramSearchCaseInsensitive('Hello World','hello');

结果：

1

ngramSearchUTF8​

提供了一个 UTF-8 变体的 ngramSearch，假设 needle 和 haystack 为 UTF-8 编码的字符串。

语法

ngramSearchUTF8(haystack, needle)

参数

返回值

  • 表示 needle 在 haystack 中的可能性的值，范围为 0 到 1。 Float32

结果越大，needle 出现在 haystack 中的可能性越大。

示例

查询：

SELECT ngramSearchUTF8('абвгдеёжз', 'гдеёзд');

结果：

0.5

ngramSearchCaseInsensitiveUTF8​

提供了一个不区分大小写的 ngramSearchUTF8 变体。

语法

ngramSearchCaseInsensitiveUTF8(haystack, needle)

参数

返回值

  • 表示 needle 在 haystack 中的可能性的值，范围为 0 到 1。 Float32

结果越大，needle 出现在 haystack 中的可能性越大。

示例

查询：

SELECT ngramSearchCaseInsensitiveUTF8('абвГДЕёжз', 'АбвгдЕЁжз');

结果：

0.57142854

countSubstrings​

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

函数 countSubstringsCaseInsensitive 和 countSubstringsCaseInsensitiveUTF8 提供了此函数的不区分大小写和不区分大小写 + UTF-8 变体。

语法

countSubstrings(haystack, needle[, start_pos])

参数

  • haystack — 进行搜索的字符串。 字符串 或 枚举。
  • needle — 要搜索的子字符串。 字符串。
  • start_pos – 在 haystack 中开始搜索的位置（基于 1）。 UInt。 可选。

返回值

  • 出现的次数。 UInt64。

示例

SELECT countSubstrings('aaaa', 'aa');

结果：

┌─countSubstrings('aaaa', 'aa')─┐
│                             2 │
└───────────────────────────────┘

带有 start_pos 参数的示例：

SELECT countSubstrings('abc___abc', 'abc', 4);

结果：

┌─countSubstrings('abc___abc', 'abc', 4)─┐
│                                      1 │
└────────────────────────────────────────┘

countSubstringsCaseInsensitive​

返回子字符串 needle 在字符串 haystack 中出现的次数。 忽略大小写。

语法

countSubstringsCaseInsensitive(haystack, needle[, start_pos])

参数

  • haystack — 进行搜索的字符串。 字符串 或 枚举。
  • needle — 要搜索的子字符串。 字符串。
  • start_pos – 在 haystack 中开始搜索的位置（基于 1）。 UInt。 可选。

返回值

  • 出现的次数。 UInt64。

示例

查询：

SELECT countSubstringsCaseInsensitive('AAAA', 'aa');

结果：

┌─countSubstringsCaseInsensitive('AAAA', 'aa')─┐
│                                            2 │
└──────────────────────────────────────────────┘

带有 start_pos 参数的示例：

查询：

SELECT countSubstringsCaseInsensitive('abc___ABC___abc', 'abc', 4);

结果：

┌─countSubstringsCaseInsensitive('abc___ABC___abc', 'abc', 4)─┐
│                                                           2 │
└─────────────────────────────────────────────────────────────┘

countSubstringsCaseInsensitiveUTF8​

返回子字符串 needle 在字符串 haystack 中出现的次数。 忽略大小写，并假设 haystack 是 UTF8 字符串。

语法

countSubstringsCaseInsensitiveUTF8(haystack, needle[, start_pos])

参数

  • haystack — 进行搜索的 UTF-8 字符串。 字符串 或 枚举。
  • needle — 要搜索的子字符串。 字符串。
  • start_pos – 在 haystack 中开始搜索的位置（基于 1）。 UInt。 可选。

返回值

  • 出现的次数。 UInt64。

示例

查询：

SELECT countSubstringsCaseInsensitiveUTF8('ложка, кошка, картошка', 'КА');

结果：

┌─countSubstringsCaseInsensitiveUTF8('ложка, кошка, картошка', 'КА')─┐
│                                                                  4 │
└────────────────────────────────────────────────────────────────────┘

带有 start_pos 参数的示例：

查询：

SELECT countSubstringsCaseInsensitiveUTF8('ложка, кошка, картошка', 'КА', 13);

结果：

┌─countSubstringsCaseInsensitiveUTF8('ложка, кошка, картошка', 'КА', 13)─┐
│                                                                      2 │
└────────────────────────────────────────────────────────────────────────┘

countMatches​

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

此函数的行为取决于 ClickHouse 版本：

  • 在版本 < v25.6 时，countMatches 会在第一个空匹配处停止计数，即使模式接受。
  • 在版本 >= 25.6 时，countMatches 会在出现空匹配时继续执行。 可以使用设置 count_matches_stop_at_empty_match = true 恢复遗留行为。

语法

countMatches(haystack, pattern)

参数

返回值

  • 匹配的数量。 UInt64。

示例

SELECT countMatches('foobar.com', 'o+');

结果：

┌─countMatches('foobar.com', 'o+')─┐
│                                2 │
└──────────────────────────────────┘

SELECT countMatches('aaaa', 'aa');

结果：

┌─countMatches('aaaa', 'aa')────┐
│                             2 │
└───────────────────────────────┘

countMatchesCaseInsensitive​

返回字符串 haystack 中与模式匹配的正则表达式的数量，类似于 countMatches，但匹配时忽略大小写。

语法

countMatchesCaseInsensitive(haystack, pattern)

参数

返回值

  • 匹配的数量。 UInt64。

示例

查询：

SELECT countMatchesCaseInsensitive('AAAA', 'aa');

结果：

┌─countMatchesCaseInsensitive('AAAA', 'aa')────┐
│                                            2 │
└──────────────────────────────────────────────┘

regexpExtract​

提取在 haystack 中与正则表达式模式匹配并对应于正则表达式组索引的第一个字符串。

语法

regexpExtract(haystack, pattern[, index])

别名：REGEXP_EXTRACT(haystack, pattern[, index])。

参数

  • haystack — 要匹配正则表达式模式的字符串。 字符串。
  • pattern — 字符串，正则表达式，必须是常量。 字符串。
  • index – 整数，取值大于或等于 0，默认值为 1。它表示要提取的正则表达式组。 UInt 或 Int。 可选。

返回值

pattern 可能包含多个正则表达式组，index 指示要提取的正则表达式组。索引 0 意味着匹配整个正则表达式。 字符串。

示例

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

结果：

┌─regexpExtract('100-200', '(\\d+)-(\\d+)', 1)─┬─regexpExtract('100-200', '(\\d+)-(\\d+)', 2)─┬─regexpExtract('100-200', '(\\d+)-(\\d+)', 0)─┬─regexpExtract('100-200', '(\\d+)-(\\d+)')─┐
│ 100                                          │ 200                                          │ 100-200                                      │ 100                                       │
└──────────────────────────────────────────────┴──────────────────────────────────────────────┴──────────────────────────────────────────────┴───────────────────────────────────────────┘

hasSubsequence​

如果 needle 是 haystack 的子序列，则返回 1， 否则返回 0。 字符串的子序列是可以由给定字符串派生的序列，通过删除零或多个元素而不更改剩余元素的顺序。 语法

hasSubsequence(haystack, needle)

参数

  • haystack — 进行搜索的字符串。 字符串。
  • needle — 要搜索的子序列。 字符串。

返回值

  • 如果 needle 是 haystack 的子序列，则返回 1，否则返回 0。 UInt8。

示例

查询：

SELECT hasSubsequence('garbage', 'arg');

结果：

┌─hasSubsequence('garbage', 'arg')─┐
│                                1 │
└──────────────────────────────────┘

hasSubsequenceCaseInsensitive​

类似于 hasSubsequence，但不区分大小写搜索。

语法

hasSubsequenceCaseInsensitive(haystack, needle)

参数

  • haystack — 进行搜索的字符串。 字符串。
  • needle — 要搜索的子序列。 字符串。

返回值

  • 如果 needle 是 haystack 的子序列，则返回 1，否则返回 0。 UInt8。

示例

查询：

SELECT hasSubsequenceCaseInsensitive('garbage', 'ARG');

结果：

┌─hasSubsequenceCaseInsensitive('garbage', 'ARG')─┐
│                                               1 │
└─────────────────────────────────────────────────┘

hasSubsequenceUTF8​

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

语法

hasSubsequenceUTF8(haystack, needle)

参数

  • haystack — 进行搜索的字符串。 UTF-8 编码的 字符串。
  • needle — 要搜索的子序列。 UTF-8 编码的 字符串。

返回值

  • 如果 needle 是 haystack 的子序列，则返回 1，否则返回 0。 UInt8。

查询：

示例

SELECT hasSubsequenceUTF8('ClickHouse - столбцовая система управления базами данных', 'система');

结果：

┌─hasSubsequenceUTF8('ClickHouse - столбцовая система управления базами данных', 'система')─┐
│                                                                                         1 │
└───────────────────────────────────────────────────────────────────────────────────────────┘

hasSubsequenceCaseInsensitiveUTF8​

类似于 hasSubsequenceUTF8，但不区分大小写搜索。

语法

hasSubsequenceCaseInsensitiveUTF8(haystack, needle)

参数

  • haystack — 进行搜索的字符串。 UTF-8 编码的 字符串。
  • needle — 要搜索的子序列。 UTF-8 编码的 字符串。

返回值

  • 如果 needle 是 haystack 的子序列，则返回 1，否则返回 0。 UInt8。

示例

查询：

SELECT hasSubsequenceCaseInsensitiveUTF8('ClickHouse - столбцовая система управления базами данных', 'СИСТЕМА');

结果：

┌─hasSubsequenceCaseInsensitiveUTF8('ClickHouse - столбцовая система управления базами данных', 'СИСТЕМА')─┐
│                                                                                                        1 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────┘

hasToken​

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

语法

hasToken(haystack, token)

参数

  • haystack：进行搜索的字符串。 字符串 或 枚举。
  • token：两个非字母数字 ASCII 字符之间的最大长度子字符串（或 haystack 的边界）。

返回值

  • 如果 token 存在于 haystack 中，则返回 1，否则返回 0。 UInt8。

实现细节

Token 必须是常量字符串。受 tokenbf_v1 索引特殊化支持。

示例

查询：

SELECT hasToken('Hello World','Hello');

1

hasTokenOrNull​

如果给定的 token 存在，则返回 1，如果不存在，则返回 0，如果 token 格式不正确，则返回 null。

语法

hasTokenOrNull(haystack, token)

参数

  • haystack：进行搜索的字符串。 字符串 或 枚举。
  • token：两个非字母数字 ASCII 字符之间的最大长度子字符串（或 haystack 的边界）。

返回值

  • 如果 token 存在于 haystack 中，则返回 1，如果不存在，则返回 0；如果 token 格式不正确，则返回 null。 UInt8。

实现细节

Token 必须是常量字符串。受 tokenbf_v1 索引特殊化支持。

示例

如果 hasToken 对格式不正确的 token 抛出错误，hasTokenOrNull 则返回格式不正确的 token 对应的 null。

查询：

SELECT hasTokenOrNull('Hello World','Hello,World');

null

hasTokenCaseInsensitive​

如果给定的 token 存在于 haystack 中，则返回 1，否则返回 0。 忽略大小写。

语法

hasTokenCaseInsensitive(haystack, token)

参数

  • haystack：进行搜索的字符串。 字符串 或 枚举。
  • token：两个非字母数字 ASCII 字符之间的最大长度子字符串（或 haystack 的边界）。

返回值

  • 如果 token 存在于 haystack 中，则返回 1，否则返回 0。 UInt8。

实现细节

Token 必须是常量字符串。受 tokenbf_v1 索引特殊化支持。

示例

查询：

SELECT hasTokenCaseInsensitive('Hello World','hello');

1

hasTokenCaseInsensitiveOrNull​

如果给定的 token 存在于 haystack 中，则返回 1，否则返回 0。 忽略大小写，并在 token 格式不正确时返回 null。

语法

hasTokenCaseInsensitiveOrNull(haystack, token)

参数

  • haystack：进行搜索的字符串。 字符串 或 枚举。
  • token：两个非字母数字 ASCII 字符之间的最大长度子字符串（或 haystack 的边界）。

返回值

  • 如果 token 存在于 haystack 中，则返回 1，如果 token 不存在，则返回 0，否则如果 token 格式不正确，则返回 null。 UInt8。

实现细节

Token 必须是常量字符串。受 tokenbf_v1 索引特殊化支持。

示例

如果 hasTokenCaseInsensitive 对格式不正确的 token 抛出错误，hasTokenCaseInsensitiveOrNull 则返回格式不正确的 token 对应的 null。

查询：

SELECT hasTokenCaseInsensitiveOrNull('Hello World','hello,world');

null