Clickhouse 字符串函数使用总结—— Clickhouse基础篇（七）

判空

SELECT
    empty('') AS res1,
    empty('a') AS res2

Query id: 9aae1f36-5acb-4dbf-93a9-efe4a6d0a671

┌─res1─┬─res2─┐
│    1 │    0 │
└──────┴──────┘

非空判断

SELECT
    notEmpty('') AS res1,
    notEmpty('a') AS res2

Query id: 140a6942-c36e-48dc-8fa7-7699f67386e4

┌─res1─┬─res2─┐
│    0 │    1 │
└──────┴──────┘

字符串长度

SELECT
    length('') AS res1,
    length('abc') AS res2,
    length(NULL) AS res3

Query id: e8454e1c-3307-4295-81fc-4d5dd846fb7e

┌─res1─┬─res2─┬─res3─┐
│    0 │    3 │ ᴺᵁᴸᴸ │
└──────┴──────┴──────┘

左补齐字符串

将字符串 10 用字符串 s 补齐至7位，

SELECT leftPad('10', 7, 's') AS res1

Query id: ae7bfd49-bb75-4b0b-a4af-061182ac598d

┌─res1────┐
│ sssss10 │
└─────────┘

右补齐字符串

SELECT rightPad('10', 7, 's') AS res1

Query id: ae7bfd49-bb75-4b0b-a4af-061182ac598d

┌─res1────┐
│ 10sssss │
└─────────┘

字符串转小写

SELECT lower('aBC') AS res

Query id: c41b8753-edcf-4984-816c-ee70febc0b64

┌─res─┐
│ abc │
└─────┘

字符串转大写

SELECT upper('abc') AS res

Query id: c41b8753-edcf-4984-816c-ee70febc0b64

┌─res─┐
│ ABC │
└─────┘

重复字符串

SELECT repeat('abc', 3) AS res

Query id: 0f519227-cda4-49e1-a003-ede5862c5a3a

┌─res───────┐
│ abcabcabc │
└───────────┘

拼接字符串函数

SELECT concat('HELLO', ',', 'World!') AS res

Query id: 33fade2d-7b20-4da9-8b1c-4ffb40bdb3dd

┌─res──────────┐
│ HELLO,World! │
└──────────────┘

计算子串

SELECT
    'abcdef' AS s,
    substring(s, 1, 3) AS res

Query id: 7e36fc5d-b054-40c1-b2a5-84bb88c1f8db

┌─s──────┬─res─┐
│ abcdef │ abc │
└────────┴─────┘

base64编码

SELECT base64Encode('123456') AS res

Query id: 106b426e-eea5-45af-aca5-0bb6243cfa80

┌─res──────┐
│ MTIzNDU2 │
└──────────┘

base64解码

SELECT base64Decode('MTIzNDU2') AS res;

Query id: 106b426e-eea5-45af-aca5-0bb6243cfa80

┌─res──────┐
│ 123456   │
└──────────┘

判断开头字符串

SELECT startsWith('abc', 'a') AS res

Query id: 64b2af5f-a4c4-440a-a019-64675554e060

┌─res─┐
│   1 │
└─────┘

判断结尾字符串

SELECT startsWith('abc', 'a') AS res

Query id: 64b2af5f-a4c4-440a-a019-64675554e060

┌─res─┐
│   0 │
└─────┘

删除空白字符

SELECT trimBoth('   a bc  ') AS res

Query id: 074bfb13-6211-47b0-964f-c9c3005a6110

┌─res──┐
│ a bc │
└──────┘

从HTML提取纯文本

SELECT extractTextFromHTML('<a>text</a>') AS res

Query id: 7389e44a-5726-4216-8987-9105c32a1b30

┌─res──┐
│ text │
└──────┘

字符串部分替换

SELECT replaceOne('abc****defg****', '*', '$') AS res

Query id: 6e9cdca9-9b37-4824-ab3a-0d712d8d81aa

┌─res─────────────┐
│ abc$***defg**** │
└─────────────────┘

字符串全部替换

SELECT replaceAll('abc****defg****', '*', '$') AS res

Query id: 442b28c2-dae8-4de8-9738-f41241632255

┌─res─────────────┐
│ abc$$$$defg$$$$ │
└─────────────────┘

字符串正则部分替换

SELECT replaceRegexpOne('abc123de09', '(\\d)', '$') AS res

Query id: 7409ff71-48f2-4fc5-b752-93cd80a16e34

┌─res────────┐
│ abc$23de09 │
└────────────┘

字符串正则全部替换

SELECT replaceRegexpAll('abc123de09', '(\\d)', '$') AS res

Query id: 7409ff71-48f2-4fc5-b752-93cd80a16e34

┌─res────────┐
│ abc$$$de$$ │
└────────────┘

计算子串下标

SELECT position('123456789', '7') AS res

Query id: 84ed92af-bb3c-45bf-8800-c0c84ac3d91f

┌─res─┐
│   7 │
└─────┘

正则匹配

SELECT match('abcccddd', '(^abc)') AS res

Query id: cc18551b-9a46-4554-83ff-fb3764e5a033

┌─res─┐
│   1 │
└─────┘

模糊匹配

SELECT
    'abc' LIKE 'a%' AS res1,
    '12345' LIKE '__3__' AS res2

Query id: 54992c14-41e5-4145-9edd-ad26af67110d

┌─res1─┬─res2─┐
│    1 │    1 │
└──────┴──────┘

正则匹配次数

SELECT
    countMatches('foobar.com', 'o+') AS res1,
    countMatches('aaaa', 'aa') AS res2

Query id: d245a3d8-f8bd-446d-aeca-0f54184ed24e

┌─res1─┬─res2─┐
│    2 │    2 │
└──────┴──────┘