数据库原理与选型 / 系统课时
常用文本函数
常用文本函数
本课解决 SQLite 中字符串截取、清洗、替换、大小写转换和格式化输出的问题。完成后,你可以直接在查询中整理文本,并得到可验证的结果。
学习目标
- 使用
length与substr获取文本长度和片段。 - 使用
trim、replace、lower、upper清洗文本。 - 使用
printf生成固定格式的展示字符串,并检查空字符串与NULL的边界。
获取长度与截取文本
length(X) 返回文本字符数;substr(X, 起点, 长度) 从指定位置截取。起点从 1 开始,负数表示从末尾倒数。第三个参数是截取长度,不是结束位置;省略时一直截取到末尾。citeturn565233view0
以下 SQL 可在已连接 SQLite 的查询窗口逐段执行,不依赖已有表。所有输入都是固定测试值,预期结果列出列名与数据;客户端的分隔符和对齐方式可能不同。
WITH sample(txt) AS (
-- 固定样本无需建表,空格也计入字符数
VALUES ('SQLite 教程')
)
SELECT
length(txt) AS char_count,
substr(txt, 1, 6) AS prefix,
-- 省略长度时,从起点截取到末尾
substr(txt, -2) AS suffix
FROM sample;预期结果
char_count | prefix | suffix
9 | SQLite | 教程这里按 Unicode 码点计数,而不是按 UTF-8 字节数计数,因此两个汉字分别算一个字符,中间的空格也计数。若输入是 BLOB,length 返回字节数,substr 的位置和长度也按字节计算。citeturn565233view0
清理与替换文本
trim(X) 默认删除两端普通空格,不删除中间空格,也不会自动清理制表符、换行或全角空格。replace(X, 查找值, 替换值) 会替换所有匹配内容,区分大小写,不支持正则表达式。citeturn565233view0
-- 只处理查询结果,不会修改原始数据
SELECT
trim(' sqlite ') AS cleaned,
replace('2026/07/30', '/', '-') AS normalized;预期结果
cleaned | normalized
sqlite | 2026-07-30trim(X, Y) 中的 Y 是要删除的字符集合,不是一个必须整体匹配的字符串。例如 trim('abSQLiteba', 'ab') 得到 SQLite,因为两端的 a、b 都属于删除集合。citeturn565233view0
日期示例只把分隔符从 / 换成 -,不能据此认定输入日期有效。用于导入数据时,应把“统一文字格式”和“验证日期是否合法”分开处理。
转换大小写
-- 使用 ASCII 字母,避免混入非 ASCII 大小写规则
SELECT
lower('Sqlite') AS lower_text,
upper('Sqlite') AS upper_text;预期结果
lower_text | upper_text
sqlite | SQLITESQLite 内置大小写转换只覆盖 ASCII 字符。若需要完整的 Unicode 大小写规则,通常要启用 ICU 扩展或在应用层处理;不要把上述英文样本的结果直接推广到所有语言。citeturn565233view0
格式化输出
printf 使用格式占位符拼接文本,适合补零、控制小数位或生成展示字段。%04d 以最小宽度 4 输出整数,不足宽度时补零,超过宽度不会截断;%.2f 将数值显示为两位小数。citeturn565233view1
-- %04d 指定最小宽度;%.2f 控制展示的小数位数
SELECT printf('订单 %04d:¥%.2f', 7, 19.9) AS display_text;预期结果
display_text
订单 0007:¥19.90格式化结果适合展示,不应替代原始数值字段。金额仍应以数值类型参与计算和排序。例如本例保留原始金额 19.9,只在展示时生成带货币符号和两位小数的文本。
组合清洗并验证边界
现在把函数用于同一个标签字段:去掉两端空格、统一为小写,再检查长度。约定仅在展示时把缺失值显示为空文本;coalesce(txt, '') 在 txt 为 NULL 时取空字符串,否则保留原值。citeturn565233view0
WITH sample(id, txt) AS (
-- 同时覆盖正常文本、纯空格和缺失值
VALUES (1, ' Sqlite '), (2, ' '), (3, NULL)
),
cleaned AS (
-- 本例仅为展示,把 NULL 明确转换成空字符串
SELECT id, lower(trim(coalesce(txt, ''))) AS txt
FROM sample
)
SELECT
id,
printf('[%s]', txt) AS visible_text,
length(txt) AS char_count
FROM cleaned
ORDER BY id;预期结果
id | visible_text | char_count
1 | [sqlite] | 6
2 | [] | 0
3 | [] | 0方括号只用来让空字符串可见,不属于清洗后的文本。第一行确认空格被去掉且大小写已统一;后两行确认纯空格和缺失值都按本例约定显示为空。
length(NULL) 返回 NULL,而 length('') 返回 0,两者本来不同。只有业务明确允许时才用空字符串兜底;需要区分“未填写”和“填写为空”时,应保留原始 NULL,不要用上述展示结果覆盖原始字段。citeturn565233view0