数据库原理与选型 / 系统课时

常用文本函数

常用文本函数

本课解决 SQLite 中字符串截取、清洗、替换、大小写转换和格式化输出的问题。完成后,你可以直接在查询中整理文本,并得到可验证的结果。

学习目标

  • 使用 length 与 substr 获取文本长度和片段。
  • 使用 trim、replace、lower、upper 清洗文本。
  • 使用 printf 生成固定格式的展示字符串,并检查空字符串与 NULL 的边界。

获取长度与截取文本

length(X) 返回文本字符数;substr(X, 起点, 长度) 从指定位置截取。起点从 1 开始,负数表示从末尾倒数。第三个参数是截取长度,不是结束位置;省略时一直截取到末尾。citeturn565233view0

以下 SQL 可在已连接 SQLite 的查询窗口逐段执行,不依赖已有表。所有输入都是固定测试值,预期结果列出列名与数据;客户端的分隔符和对齐方式可能不同。

sql
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;

预期结果

text
char_count | prefix | suffix
9          | SQLite | 教程

这里按 Unicode 码点计数,而不是按 UTF-8 字节数计数,因此两个汉字分别算一个字符,中间的空格也计数。若输入是 BLOB,length 返回字节数,substr 的位置和长度也按字节计算。citeturn565233view0

清理与替换文本

trim(X) 默认删除两端普通空格,不删除中间空格,也不会自动清理制表符、换行或全角空格。replace(X, 查找值, 替换值) 会替换所有匹配内容,区分大小写,不支持正则表达式。citeturn565233view0

sql
-- 只处理查询结果,不会修改原始数据
SELECT
  trim('  sqlite  ') AS cleaned,
  replace('2026/07/30', '/', '-') AS normalized;

预期结果

text
cleaned | normalized
sqlite  | 2026-07-30

trim(X, Y) 中的 Y 是要删除的字符集合,不是一个必须整体匹配的字符串。例如 trim('abSQLiteba', 'ab') 得到 SQLite,因为两端的 a、b 都属于删除集合。citeturn565233view0

日期示例只把分隔符从 / 换成 -,不能据此认定输入日期有效。用于导入数据时,应把“统一文字格式”和“验证日期是否合法”分开处理。

转换大小写

sql
-- 使用 ASCII 字母,避免混入非 ASCII 大小写规则
SELECT
  lower('Sqlite') AS lower_text,
  upper('Sqlite') AS upper_text;

预期结果

text
lower_text | upper_text
sqlite     | SQLITE

SQLite 内置大小写转换只覆盖 ASCII 字符。若需要完整的 Unicode 大小写规则,通常要启用 ICU 扩展或在应用层处理;不要把上述英文样本的结果直接推广到所有语言。citeturn565233view0

格式化输出

printf 使用格式占位符拼接文本,适合补零、控制小数位或生成展示字段。%04d 以最小宽度 4 输出整数,不足宽度时补零,超过宽度不会截断;%.2f 将数值显示为两位小数。citeturn565233view1

sql
-- %04d 指定最小宽度;%.2f 控制展示的小数位数
SELECT printf('订单 %04d:¥%.2f', 7, 19.9) AS display_text;

预期结果

text
display_text
订单 0007:¥19.90

格式化结果适合展示,不应替代原始数值字段。金额仍应以数值类型参与计算和排序。例如本例保留原始金额 19.9,只在展示时生成带货币符号和两位小数的文本。

组合清洗并验证边界

现在把函数用于同一个标签字段:去掉两端空格、统一为小写,再检查长度。约定仅在展示时把缺失值显示为空文本;coalesce(txt, '') 在 txt 为 NULL 时取空字符串,否则保留原值。citeturn565233view0

sql
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;

预期结果

text
id | visible_text | char_count
1  | [sqlite]     | 6
2  | []           | 0
3  | []           | 0

方括号只用来让空字符串可见,不属于清洗后的文本。第一行确认空格被去掉且大小写已统一;后两行确认纯空格和缺失值都按本例约定显示为空。

length(NULL) 返回 NULL,而 length('') 返回 0,两者本来不同。只有业务明确允许时才用空字符串兜底;需要区分“未填写”和“填写为空”时,应保留原始 NULL,不要用上述展示结果覆盖原始字段。citeturn565233view0

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

常用文本函数

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通