Lakehouse 字符串处理指南

概述

字符串处理是日志分析、用户行为分析和数据清洗中最常见的需求之一。云器 Lakehouse 提供完整的字符串函数支持，包括截取与拼接、正则提取与替换、模糊匹配以及字符串聚合。本指南按业务场景分类，帮助你快速掌握高效的字符串处理方法。

涉及的 SQL 命令

SUBSTR()

SUBSTRING()

CONCAT()

CONCAT_WS()

LOCATE()

INSTR()

REPLACE()

REGEXP_EXTRACT()

REGEXP_REPLACE()

SPLIT()

LATERAL VIEW EXPLODE()

LIKE

RLIKE

GROUP_CONCAT()

WM_CONCAT()

UPPER()

LOWER()

TRIM()

LTRIM()

RTRIM()

LENGTH()

前置准备

以下示例使用模拟的访问日志表

doc_str_logs

-- 创建测试表 CREATE TABLE IF NOT EXISTS doc_str_logs ( log_id INT, user_id STRING, url STRING, tags STRING, content STRING ); -- 插入测试数据 INSERT INTO doc_str_logs VALUES (1, 'u001', 'https://www.example.com/product/detail?id=123', 'tech,mobile,review', 'Hello World! This is a test.'), (2, 'u002', 'https://shop.example.com/category/shoes?color=red', 'fashion,shoes,sale', ' spaces around '), (3, 'u003', 'https://www.example.com/blog/post?tag=python', 'tech,python,tutorial', 'Python is great!'), (4, 'u001', 'https://api.example.com/v2/users?page=1', 'api,backend,rest', 'API response: {"status":"ok"}'), (5, 'u004', 'https://www.example.com/search?q=lakehouse', 'data,lakehouse,cloud', 'Lakehouse 数据平台'), (6, 'u002', 'https://cdn.example.com/images/logo.png', 'image,cdn,static', 'CDN资源文件'), (7, 'u005', 'https://www.example.com/product/detail?id=456', 'tech,mobile,new', '手机号：138-0013-8000，请联系'), (8, 'u003', 'https://shop.example.com/checkout?order=789', 'order,payment,checkout','Order #789 confirmed!');

场景一：截取与拼接

从 URL 中提取域名，或将多个字段拼接为复合键，是日志处理中最常见的字符串操作。

用 SUBSTR + LOCATE 提取域名

SUBSTR(str, pos, len)

LOCATE(substr, str, start)

start

https://

SELECT log_id, url, SUBSTR(url, 9, LOCATE('/', url, 9) - 9) AS domain FROM doc_str_logs ORDER BY log_id;

执行结果：

用 CONCAT 构造复合键

CONCAT(str1, str2, ...)

SELECT log_id, CONCAT(user_id, '@', REGEXP_EXTRACT(url, 'https?://([/]+)', 1)) AS user_domain_key FROM doc_str_logs ORDER BY log_id;

执行结果：

用 CONCAT_WS 拼接多个字段

CONCAT_WS(sep, str1, str2, ...)

CONCAT

NULL

SELECT log_id, url, CONCAT_WS('|', REGEXP_EXTRACT(url, 'https?://([/]+)', 1), REGEXP_EXTRACT(url, 'https?://[/?]+(/[?]*)', 1), REGEXP_EXTRACT(url, '[?](.*)', 1) ) AS url_parts FROM doc_str_logs ORDER BY log_id LIMIT 5;

执行结果（前 5 行）：

场景二：正则提取

当 URL 或日志格式较复杂时，

REGEXP_EXTRACT

SUBSTR + LOCATE

REGEXP_EXTRACT(str, pattern, group_index)

group_index

group_index

将 URL 拆分为域名、路径和查询参数

SELECT log_id, REGEXP_EXTRACT(url, 'https?://([/?]+)', 1) AS domain, REGEXP_EXTRACT(url, 'https?://[/?]+(/[?]*)', 1) AS path, REGEXP_EXTRACT(url, '[?](.*)', 1) AS query_string FROM doc_str_logs ORDER BY log_id;

执行结果：

[?](.*)

?

[?]

提取 URL 中的第一个参数键与值

SELECT log_id, REGEXP_EXTRACT(url, '[?&]([=]+)=([&]+)', 1) AS param_key, REGEXP_EXTRACT(url, '[?&][=]+=([&]+)', 1) AS param_value FROM doc_str_logs WHERE url LIKE '%?%' ORDER BY log_id;

执行结果：

场景三：正则替换与清洗

REGEXP_REPLACE(str, pattern, replacement)

pattern

replacement

去除特殊字符

保留汉字、英文字母、数字和空格，去除标点等其他字符：

SELECT log_id, content, REGEXP_REPLACE(content, '[a-zA-Z0-9一-龥 ]', '') AS cleaned FROM doc_str_logs ORDER BY log_id;

执行结果：

一-龥

脱敏手机号

将内容中的手机号（

NNN-NNNN-NNNN

SELECT log_id, content, REGEXP_REPLACE(content, '[0-9]{3}-[0-9]{4}-[0-9]{4}', 'PHONE_HIDDEN') AS masked FROM doc_str_logs ORDER BY log_id;

执行结果：

REGEXP_REPLACE

用 REPLACE 去除固定前缀

对于简单的字面量替换，

REPLACE(str, search, replacement)

SELECT log_id, REPLACE(url, 'https://', '') AS url_no_scheme FROM doc_str_logs ORDER BY log_id LIMIT 4;

执行结果（前 4 行）：

场景四：字符串分割转行

当一列中存储了多个值（如逗号分隔的标签），需要先用

SPLIT

LATERAL VIEW EXPLODE

展开标签列

SELECT log_id, tags, tag FROM doc_str_logs LATERAL VIEW EXPLODE(SPLIT(tags, ',')) t AS tag ORDER BY log_id, tag;

执行结果（前 9 行）：

统计标签出现频次

展开后即可对标签做聚合分析：

SELECT tag, COUNT(*) AS log_count FROM doc_str_logs LATERAL VIEW EXPLODE(SPLIT(tags, ',')) t AS tag GROUP BY tag ORDER BY log_count DESC, tag;

执行结果：

SPLIT(tags, ',')

LATERAL VIEW EXPLODE(...)

AS tag

如果只需要将字符串转为数组并统计元素个数，不需要展开为行，可以使用

SIZE(SPLIT(...))

SELECT log_id, tags, SPLIT(tags, ',') AS tag_array, SIZE(SPLIT(tags, ',')) AS tag_count FROM doc_str_logs ORDER BY log_id LIMIT 4;

执行结果（前 4 行）：

SPLIT(tags, ',')

tech:mobile:review

TO_JSON(SPLIT(tags, ','))

场景五：模糊匹配与过滤

LIKE：通配符匹配

LIKE

%

_

-- 筛选 URL 路径包含 /product/ 的记录 SELECT log_id, url FROM doc_str_logs WHERE url LIKE '%/product/%' ORDER BY log_id;

执行结果：

RLIKE：正则表达式匹配

RLIKE

REGEXP

-- 筛选来自 www 或 shop 子域名的访问 SELECT log_id, url FROM doc_str_logs WHERE url RLIKE 'https://(www|shop)\.example\.com' ORDER BY log_id;

执行结果：

-- 筛选 content 中包含手机号格式的记录 SELECT log_id, content FROM doc_str_logs WHERE content RLIKE '[0-9]{3}-[0-9]{4}-[0-9]{4}' ORDER BY log_id;

执行结果：

场景六：字符串聚合

将多行字符串合并为一行，常用于生成报表摘要或构造分析维度。

GROUP_CONCAT：按序聚合（支持 ORDER BY）

GROUP_CONCAT(expr ORDER BY col SEPARATOR sep)

-- 按用户聚合访问记录 ID，按 log_id 升序排列 SELECT user_id, GROUP_CONCAT(log_id ORDER BY log_id SEPARATOR ',') AS log_ids, COUNT(*) AS visit_count FROM doc_str_logs GROUP BY user_id ORDER BY user_id;

执行结果：

-- 按用户聚合访问的 tags，用分号分隔 SELECT user_id, GROUP_CONCAT(tags ORDER BY log_id SEPARATOR '; ') AS all_tags FROM doc_str_logs GROUP BY user_id ORDER BY user_id;

执行结果：

DISTINCT

ORDER BY

GROUP_CONCAT

WM_CONCAT：简洁写法

WM_CONCAT(sep, expr)

ORDER BY

SELECT user_id, WM_CONCAT(';', tags) AS all_tags FROM doc_str_logs GROUP BY user_id ORDER BY user_id;

执行结果：

WM_CONCAT

GROUP_CONCAT ... ORDER BY

GROUP_CONCAT DISTINCT：聚合唯一值

对每个用户访问过的唯一域名去重后合并：

SELECT user_id, GROUP_CONCAT(DISTINCT REGEXP_EXTRACT(url, 'https?://([/]+)', 1) SEPARATOR ', ') AS domains FROM doc_str_logs GROUP BY user_id ORDER BY user_id;

执行结果：

场景七：大小写与空格处理

数据入库时常有大小写不一致或首尾空格的问题，需要在分析前做标准化处理。

大小写转换

UPPER(str)

LOWER(str)

SELECT log_id, content, UPPER(content) AS upper_content FROM doc_str_logs WHERE log_id IN (1, 3, 8) ORDER BY log_id;

执行结果：

去除首尾空白

TRIM(str)

LTRIM

RTRIM

SELECT log_id, content, TRIM(content) AS trimmed, LTRIM(content) AS ltrimmed, RTRIM(content) AS rtrimmed FROM doc_str_logs WHERE log_id = 2;

执行结果：

组合标准化：LOWER + TRIM + LENGTH

在分组或 JOIN 之前，同时处理大小写和空格，避免因格式差异导致匹配失败：

SELECT log_id, user_id, LOWER(TRIM(user_id)) AS normalized_uid, LENGTH(TRIM(content)) AS content_len FROM doc_str_logs ORDER BY log_id;

执行结果：

LENGTH

清理测试数据

完成字符串处理验证后，建议清理测试表：

DROP TABLE IF EXISTS doc_str_logs;

UNDROP TABLE

注意事项

1. REGEXP_EXTRACT 的

   ?

   ?
   转义：正则中

   ?

   ?
   是量词，若要匹配字面量

   ?

   ?
   ，需要写成

   [?]

   [?]
   或

   \?

   \?
   。直接写

   '?(.*)'

   '?(.*)'
   会导致

   no argument for repetition operator

   no argument for repetition operator
   报错。
2. REGEXP_REPLACE 不支持反向引用：Lakehouse 的

   REGEXP_REPLACE

   REGEXP_REPLACE
   不支持

   $1

   $1
   、

   \1

   \1
   这样的捕获组反向引用，替换内容只能是字面量字符串。
3. GROUP_CONCAT 的 DISTINCT 与 ORDER BY 不兼容：同一个

   GROUP_CONCAT

   GROUP_CONCAT
   调用中不能同时使用

   DISTINCT

   DISTINCT
   和

   ORDER BY

   ORDER BY
   ，否则会报

   Distinct aggregate call and aggregate call with order by can not coexist

   Distinct aggregate call and aggregate call with order by can not coexist
   错误。
4. LIKE vs RLIKE 的性能差异：

   LIKE

   LIKE
   使用通配符，比

   RLIKE

   RLIKE
   性能更好；在不需要完整正则功能时，优先使用

   LIKE

   LIKE
   。
5. LENGTH 计算字符数而非字节数：

   LENGTH('CDN资源文件')

   LENGTH('CDN资源文件')
   返回 7（3 个英文 + 4 个汉字），不是字节长度。如需字节长度，使用

   OCTET_LENGTH

   OCTET_LENGTH
   。
6. SPLIT 分隔符是正则：

   SPLIT(str, pattern)

   SPLIT(str, pattern)
   的第二个参数是正则表达式，若分隔符是

   .

   .
   或

   |

   |
   等正则特殊字符，需要转义：

   SPLIT(str, '\\.')

   SPLIT(str, '\\.')
   。

相关文档

• 字符串函数
• 正则表达式函数
• LATERAL VIEW
• 聚合函数