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

字符串搜索函数

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

备注

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

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

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

position

返回子字符串 needle 在字符串 haystack 中的位置(以字节为单位,从 1 开始)。

语法

position(haystack, needle[, start_pos])

别名:

  • position(needle IN haystack)

参数

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

返回值

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

如果子字符串 needle 为空,则适用以下规则:

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

相同规则也适用于函数 locatepositionCaseInsensitivepositionUTF8positionCaseInsensitiveUTF8

示例

查询:

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,但参数 haystacklocate 交换位置。

此函数的行为取决于 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,但假定 haystackneedle 是 UTF-8 编码的字符串。

示例

函数 positionUTF8 正确将字符 ö(由两个点表示)计为一个 Unicode 代码点:

查询:

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

结果:

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

positionCaseInsensitiveUTF8

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

multiSearchAllPositions

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

备注

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

语法

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,但假定 haystackneedle 子字符串是 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 字符串的最左偏移量。

函数 multiSearchFirstPositionCaseInsensitivemultiSearchFirstPositionUTF8multiSearchFirstPositionCaseInsensitiveUTF8 提供了此函数的不区分大小写和/或 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,但假定 haystackneedle 是 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,但假定 haystackneedle 是 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。

函数 multiSearchFirstIndexCaseInsensitivemultiSearchFirstIndexUTF8multiSearchFirstIndexCaseInsensitiveUTF8 提供了不区分大小写和/或 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。假定 haystackneedle 是 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 字符串 HelloWorld 的第一个索引。

查询:

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。假定 haystackneedle 是 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 字符串 helloworld 的第一个索引。

查询:

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。

函数 multiSearchAnyCaseInsensitivemultiSearchAnyUTF8multiSearchAnyCaseInsensitiveUTF8 提供了不区分大小写和/或 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,但假定 haystackneedle 子字符串是 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 被启用时可以使用。

如果至少有一个字符串 needleiinput 列匹配,则返回 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,只有在所有字符串 needleiinput 列匹配时才返回 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)

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

语法

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 之间。结果越小,字符串之间越相似。

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

语法

ngramDistance(haystack, needle)

参数

返回值

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

实现细节

如果常量 needlehaystack 参数超过 32Kb,则此函数将抛出异常。如果任何非常量 haystackneedle 参数超过 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。假设 needlehaystack 字符串为 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

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

语法

ngramSearch(haystack, needle)

参数

返回值

  • 表示 needlehaystack 中的可能性的值,范围为 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)

参数

返回值

  • 表示 needlehaystack 中的可能性的值,范围为 0 到 1。 Float32

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

示例

查询:

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

结果:

1

ngramSearchUTF8

提供了一个 UTF-8 变体的 ngramSearch,假设 needlehaystack 为 UTF-8 编码的字符串。

语法

ngramSearchUTF8(haystack, needle)

参数

返回值

  • 表示 needlehaystack 中的可能性的值,范围为 0 到 1。 Float32

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

示例

查询:

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

结果:

0.5

ngramSearchCaseInsensitiveUTF8

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

语法

ngramSearchCaseInsensitiveUTF8(haystack, needle)

参数

返回值

  • 表示 needlehaystack 中的可能性的值,范围为 0 到 1。 Float32

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

示例

查询:

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

结果:

0.57142854

countSubstrings

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

函数 countSubstringsCaseInsensitivecountSubstringsCaseInsensitiveUTF8 提供了此函数的不区分大小写和不区分大小写 + 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

如果 needlehaystack 的子序列,则返回 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,但假设 haystackneedle 为 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 格式不正确,则返回 nullUInt8

实现细节

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 格式不正确,则返回 nullUInt8

实现细节

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

示例

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

查询:

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