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

判空

SELECTempty('') AS res1,empty('a') AS res2Query id: 9aae1f36-5acb-4dbf-93a9-efe4a6d0a671┌─res1─┬─res2─┐
│    1 │    0 │
└──────┴──────┘

非空判断

SELECTnotEmpty('') AS res1,notEmpty('a') AS res2Query id: 140a6942-c36e-48dc-8fa7-7699f67386e4┌─res1─┬─res2─┐
│    0 │    1 │
└──────┴──────┘

字符串长度

SELECTlength('') AS res1,length('abc') AS res2,length(NULL) AS res3Query id: e8454e1c-3307-4295-81fc-4d5dd846fb7e┌─res1─┬─res2─┬─res3─┐
│    0 │    3 │ ᴺᵁᴸᴸ │
└──────┴──────┴──────┘

左补齐字符串

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

SELECT leftPad('10', 7, 's') AS res1Query id: ae7bfd49-bb75-4b0b-a4af-061182ac598d┌─res1────┐
│ sssss10 │
└─────────┘

右补齐字符串

SELECT rightPad('10', 7, 's') AS res1Query id: ae7bfd49-bb75-4b0b-a4af-061182ac598d┌─res1────┐
│ 10sssss │
└─────────┘

字符串转小写

SELECT lower('aBC') AS resQuery id: c41b8753-edcf-4984-816c-ee70febc0b64┌─res─┐
│ abc │
└─────┘

字符串转大写

SELECT upper('abc') AS resQuery id: c41b8753-edcf-4984-816c-ee70febc0b64┌─res─┐
│ ABC │
└─────┘

重复字符串

SELECT repeat('abc', 3) AS resQuery id: 0f519227-cda4-49e1-a003-ede5862c5a3a┌─res───────┐
│ abcabcabc │
└───────────┘

拼接字符串函数

SELECT concat('HELLO', ',', 'World!') AS resQuery id: 33fade2d-7b20-4da9-8b1c-4ffb40bdb3dd┌─res──────────┐
│ HELLO,World! │
└──────────────┘

计算子串

SELECT'abcdef' AS s,substring(s, 1, 3) AS resQuery id: 7e36fc5d-b054-40c1-b2a5-84bb88c1f8db┌─s──────┬─res─┐
│ abcdef │ abc │
└────────┴─────┘

base64编码

SELECT base64Encode('123456') AS resQuery 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 resQuery id: 64b2af5f-a4c4-440a-a019-64675554e060┌─res─┐
│   1 │
└─────┘

判断结尾字符串

SELECT startsWith('abc', 'a') AS resQuery id: 64b2af5f-a4c4-440a-a019-64675554e060┌─res─┐
│   0 │
└─────┘

删除空白字符

SELECT trimBoth('   a bc  ') AS resQuery id: 074bfb13-6211-47b0-964f-c9c3005a6110┌─res──┐
│ a bc │
└──────┘

从HTML提取纯文本

SELECT extractTextFromHTML('<a>text</a>') AS resQuery id: 7389e44a-5726-4216-8987-9105c32a1b30┌─res──┐
│ text │
└──────┘

字符串部分替换

SELECT replaceOne('abc****defg****', '*', '$') AS resQuery id: 6e9cdca9-9b37-4824-ab3a-0d712d8d81aa┌─res─────────────┐
│ abc$***defg**** │
└─────────────────┘

字符串全部替换

SELECT replaceAll('abc****defg****', '*', '$') AS resQuery id: 442b28c2-dae8-4de8-9738-f41241632255┌─res─────────────┐
│ abc$$$$defg$$$$ │
└─────────────────┘

字符串正则部分替换

SELECT replaceRegexpOne('abc123de09', '(\\d)', '$') AS resQuery id: 7409ff71-48f2-4fc5-b752-93cd80a16e34┌─res────────┐
│ abc$23de09 │
└────────────┘

字符串正则全部替换

SELECT replaceRegexpAll('abc123de09', '(\\d)', '$') AS resQuery id: 7409ff71-48f2-4fc5-b752-93cd80a16e34┌─res────────┐
│ abc$$$de$$ │
└────────────┘

计算子串下标

SELECT position('123456789', '7') AS resQuery id: 84ed92af-bb3c-45bf-8800-c0c84ac3d91f┌─res─┐
│   7 │
└─────┘

正则匹配

SELECT match('abcccddd', '(^abc)') AS resQuery id: cc18551b-9a46-4554-83ff-fb3764e5a033┌─res─┐
│   1 │
└─────┘

模糊匹配

SELECT'abc' LIKE 'a%' AS res1,'12345' LIKE '__3__' AS res2Query id: 54992c14-41e5-4145-9edd-ad26af67110d┌─res1─┬─res2─┐
│    1 │    1 │
└──────┴──────┘

正则匹配次数

SELECTcountMatches('foobar.com', 'o+') AS res1,countMatches('aaaa', 'aa') AS res2Query id: d245a3d8-f8bd-446d-aeca-0f54184ed24e┌─res1─┬─res2─┐
│    2 │    2 │
└──────┴──────┘