SQL入门：字符串函数实战指南

最新推荐文章于 2025-10-28 10:45:12 发布

原创最新推荐文章于 2025-10-28 10:45:12 发布 · 1k 阅读

29 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #etl工程师 #sql #数据仓库 #etl #大数据 #数据库开发

SQL基础专栏收录该内容

31 篇文章

订阅专栏

字符串是 SQL 中最常见的数据类型之一（如用户名、商品名称、地址等），字符串函数则用于对字符串进行 “处理、提取、转换” 等操作 —— 比如 “截取手机号后 4 位”“将用户名转为大写”“拼接姓名和手机号”。本文聚焦标准 SQL 通用的核心字符串函数，结合生活场景和实例讲解，同时补充主流数据库的差异点，确保小白也能轻松理解。

一、先明确：SQL 中的字符串类型

在学习函数前，需先了解常见的字符串类型（不同数据库命名略有差异，但逻辑一致）：

类型分类	标准含义	示例	常见数据库对应类型
CHAR(n)	固定长度字符串（不足补空格）	'user123'（n=10 时补 3 个空格）	MySQL/SQL Server: CHAR
VARCHAR(n)	可变长度字符串（按实际长度存储	'user123'（仅占 7 个字符）	所有主流数据库: VARCHAR
TEXT	长文本字符串（存储大量文本）	一篇文章内容	MySQL: TEXT；PostgreSQL: TEXT

字符串函数的核心作用：拼接、截取、替换、查找、转换大小写、计算长度等。

二、标准 SQL 核心字符串函数（通用）

以下函数是 SQL 标准定义的基础功能，在主流数据库中兼容性较高，结合 “用户表（users）” 和 “商品表（products）” 实例讲解（表结构参考：users含username（用户名）、phone（手机号）；products含name（商品名）、description（描述））。

1. 字符串拼接：将多个字符串合并为一个

用于 “组合信息”，比如拼接 “用户名 + ID”“姓名 + 性别”，最常用的是CONCAT函数。

（1）`CONCAT(字符串1, 字符串2, ...)`

作用：将多个字符串按顺序拼接，若有NULL值，结果整体为NULL。
语法：CONCAT( str1, str2, [str3, ...] )
示例：拼接用户名和用户 ID（假设user_id是数字，会自动转为字符串）。

SELECT 
  user_id,
  username,
  CONCAT(username, '(ID:', user_id, ')') AS 带ID的用户名 -- 拼接多个字符串
FROM users;

示例结果：若user_id=101、username='张三'，则 “带 ID 的用户名” 为张三(ID:101)。

（2）`CONCAT_WS(分隔符, 字符串1, 字符串2, ...)`（扩展函数，兼容性高）

作用：用指定 “分隔符” 拼接多个字符串，自动忽略NULL值（比CONCAT更灵活）。
语法：CONCAT_WS( separator, str1, str2, [str3, ...] )
示例：拼接用户的姓名、年龄、性别，用 “|” 分隔，忽略NULL值。

SELECT 
  username,
  age,
  gender,
  CONCAT_WS('|', username, age, gender) AS 用户信息 -- 年龄为NULL时，忽略该部分
FROM users;

示例结果：若username='李四'、age=25、gender=NULL，则 “用户信息” 为李四|25。

2. 字符串长度：计算字符或字节数

用于 “判断字符串长度”，比如 “验证手机号是否为 11 位”“限制用户名长度不超过 20 字符”。

函数	作用	语法示例	结果（字符串 'abc123'）
LENGTH(字符串)	计算字节数（受字符集影响）	LENGTH('abc123')	6（UTF-8 中 1 个字母 / 数字占 1 字节）
CHAR_LENGTH(字符串)	计算字符数（与字符集无关）	CHAR_LENGTH('abc123')	6（无论什么字符集，都是 6 个字符）

实例 1：筛选用户名长度在 3-20 字符之间的用户

SELECT username
FROM users
WHERE CHAR_LENGTH(username) BETWEEN 3 AND 20; -- 按字符数筛选，更符合业务需求

实例 2：统计商品名称的平均字符长度

SELECT AVG(CHAR_LENGTH(name)) AS 商品名平均长度
FROM products;

3. 字符串截取：提取指定位置的子串

用于 “从长字符串中提取部分内容”，比如 “提取手机号后 4 位”“截取邮箱的 @前面部分”，核心函数是SUBSTRING（别名SUBSTR）。

`SUBSTRING(字符串, 起始位置, [长度])`

作用：从字符串的 “起始位置” 开始，截取指定 “长度” 的子串（起始位置从 1 开始，不是 0！）。
语法：
- 截取到末尾：SUBSTRING( str, start )
- 截取指定长度：SUBSTRING( str, start, length )
支持负数位置：起始位置为负数时，表示从字符串 “末尾” 开始计数（如 - 1 = 最后 1 个字符）。

实例 3：提取手机号后 4 位（假设手机号是 11 位字符串）

SELECT 
  phone,
  SUBSTRING(phone, -4) AS 手机号后4位 -- 从末尾第4位开始，截取到末尾
FROM users;

示例结果：若phone='13812345678'，则 “手机号后 4 位” 为5678。

实例 4：截取邮箱的 “用户名部分”（@前面的内容）

SELECT 
  email,
  SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS 邮箱用户名
FROM users;

逻辑：POSITION('@' IN email)获取 @在邮箱中的位置（如zhangsan@xxx.com中 @在 9 的位置），再截取从 1 到 8 的子串，即zhangsan。

4. 字符串查找：定位子串的位置

用于 “判断字符串中是否包含某个子串” 或 “获取子串的位置”，核心函数是POSITION和INSTR。

（1）`POSITION(子串 IN 字符串)`

作用：返回子串在字符串中 “第一次出现的起始位置”，若不存在则返回 0（位置从 1 开始）。
语法：POSITION( substr IN str )

（2）`INSTR(字符串, 子串)`（扩展函数，MySQL 常用）

作用：与POSITION类似，返回子串在字符串中第一次出现的位置，语法顺序相反。
语法：INSTR( str, substr )

实例 5：筛选 “商品名称包含‘手机’” 的商品（模糊查询辅助）

-- 方法1：用POSITION
SELECT name
FROM products
WHERE POSITION('手机' IN name) > 0; -- 包含“手机”则位置>0

-- 方法2：用LIKE（等价效果，更常用）
SELECT name
FROM products
WHERE name LIKE '%手机%';

实例 6：获取 “商品描述中‘新品’第一次出现的位置”

SELECT 
  name,
  description,
  POSITION('新品' IN description) AS 新品位置
FROM products;

示例结果：若描述为 “2024 新品智能手机”，则 “新品位置” 为 5。

5. 字符串替换：替换指定子串

用于 “修改字符串中的部分内容”，比如 “将手机号中间 4 位替换为 *”“统一替换错别字”，核心函数是REPLACE。

`REPLACE(字符串, 旧子串, 新子串)`

作用：将字符串中所有 “旧子串” 替换为 “新子串”，若旧子串不存在则返回原字符串。
语法：REPLACE( str, old_substr, new_substr )

实例 7：手机号脱敏（中间 4 位替换为 ****）

SELECT 
  phone,
  CONCAT(
    SUBSTRING(phone, 1, 3), -- 前3位
    '****',                 -- 中间替换为*
    SUBSTRING(phone, 8)     -- 后4位
  ) AS 脱敏手机号
FROM users;

示例结果：13812345678→138****5678。

实例 8：统一替换商品描述中的 “桔子” 为 “橘子”

UPDATE products
SET description = REPLACE(description, '桔子', '橘子')
WHERE POSITION('桔子' IN description) > 0;

6. 大小写转换：统一字符串大小写

用于 “标准化字符串格式”，比如 “统一用户名小写存储”“查询时忽略大小写”，核心函数是UPPER和LOWER。

函数	作用	语法示例	结果（字符串 'Hello World'）
UPPER(字符串)	转为大写	UPPER('Hello World')	HELLO WORLD
LOWER(字符串)	转为小写	LOWER('Hello World')	hello world

实例 9：查询时忽略用户名大小写（比如用户输入 “zhangsan” 或 “ZHANGSAN” 都能找到）

SELECT *
FROM users
WHERE LOWER(username) = LOWER('ZhangSan'); -- 统一转为小写后对比

实例 10：将商品名称统一转为首字母大写（需配合其他函数，以 MySQL 为例）

-- MySQL中用CONCAT+UPPER+LOWER实现首字母大写
SELECT 
  name,
  CONCAT(
    UPPER(SUBSTRING(name, 1, 1)), -- 首字母大写
    LOWER(SUBSTRING(name, 2))     -- 其余字母小写
  ) AS 首字母大写名称
FROM products;

示例结果：apple→Apple，BANANA→Banana。

7. 空格处理：去除或添加空格

用于 “清理字符串前后空格” 或 “对齐字符串”，常见函数有TRIM、LTRIM、RTRIM、LPAD、RPAD。

函数	作用	语法示例	结果（字符串 ' abc 123 '）
TRIM(字符串)	去除前后空格	TRIM(' abc 123 ')	abc 123
LTRIM(字符串)	去除左侧空格	LTRIM(' abc 123 ')	abc 123
RTRIM(字符串)	去除右侧空格	RTRIM(' abc 123 ')	abc 123
LPAD(字符串, 长度, 补位符)	左侧补位，使字符串达到指定长度	LPAD('123', 5, '0')	00123
RPAD(字符串, 长度, 补位符)	右侧补位，使字符串达到指定长度	RPAD('123', 5, '0')	12300

实例 11：清理用户输入的用户名（去除前后空格）

-- 查询时清理
SELECT TRIM(username) AS 清理后用户名 FROM users;

-- 存储时清理（插入/更新时）
INSERT INTO users (username) VALUES (TRIM('  李四  ')); -- 实际存储为'李四'

实例 12：将订单号补为 8 位（不足 8 位时左侧补 0）

SELECT 
  order_id,
  LPAD(order_id, 8, '0') AS 8位订单号
FROM orders;

示例结果：1234→00001234，56789→00056789。

三、主流数据库字符串函数差异（避坑重点）

标准 SQL 的核心函数在不同数据库中基本通用，但部分函数的名称或功能有差异，新手容易踩坑，整理高频差异点：

功能需求	MySQL 语法	PostgreSQL 语法	SQL Server 语法
字符串截取	`SUBSTRING(str, start, len)` / `SUBSTR()`	SUBSTRING(str FROM start FOR len)	SUBSTRING(str, start, len)
查找子串位置	INSTR(str, substr)	POSITION(substr IN str)	CHARINDEX(substr, str)
字符串拼接	`CONCAT()` / `CONCAT_WS()`	`\|\|+CONCAT()` / `CONCAT_WS()`	`+/CONCAT()` / `CONCAT_WS()`
首字母大写	需`CONCAT+UPPER+LOWER`组合	INITCAP(str)	UPPER(LEFT(str,1))+LOWER(SUBSTRING(str,2))
去除空格	`TRIM()` / `LTRIM()` / `RTRIM()`	`TRIM()` / `LTRIM()` / `RTRIM()`	`LTRIM()` / `RTRIM()`（`TRIM()`需指定方向）

避坑示例：字符串拼接差异

MySQL：CONCAT('a', 'b', 'c') → abc；
PostgreSQL：'a' || 'b' || 'c' → abc（||是拼接运算符）；
SQL Server：'a' + 'b' + 'c' → abc（+是拼接运算符）。

四、高频业务场景实战（综合运用）

掌握单个函数后，需结合业务场景综合使用，以下是 3 个典型场景：

场景 1：用户昵称脱敏（姓显示，名用 * 代替）

需求：对 “张三”“李四王五” 等昵称，脱敏为 “张 *”“李 **”（姓保留，名用 * 替换）。

SELECT 
  username,
  CASE 
    WHEN CHAR_LENGTH(username) = 1 THEN username -- 单字名不脱敏
    ELSE CONCAT(
      SUBSTRING(username, 1, 1), -- 保留第一个字（姓）
      REPEAT('*', CHAR_LENGTH(username) - 1) -- 名用*替换（长度=总长度-1）
    )
  END AS 脱敏昵称
FROM users;

核心逻辑：用CASE判断昵称长度，单字名直接返回，多字名保留首字，其余用REPEAT生成对应数量的 *。

场景 2：统计 “商品名称中包含‘苹果’且长度≥5” 的商品数量

需求：筛选商品名含 “苹果”，且字符长度≥5 的商品，统计总数。

SELECT COUNT(*) AS 符合条件商品数
FROM products
WHERE 
  POSITION('苹果' IN name) > 0 -- 包含“苹果”
  AND CHAR_LENGTH(name) >= 5; -- 长度≥5

示例：符合条件的商品名如 “苹果手机 13”（含 “苹果”，长度 6），不符合的如 “苹果”（长度 2）。

场景 3：生成 “用户标签”（拼接用户等级、注册年份、性别）

需求：标签格式为 “等级 - 年份 - 性别”，如 “VIP-2024 - 男”，若性别为 NULL 则显示 “未知”。

SELECT 
  user_id,
  username,
  CONCAT_WS('-', 
    user_level, -- 等级（如VIP、普通）
    YEAR(register_time), -- 注册年份
    COALESCE(gender, '未知') -- 性别，NULL替换为未知
  ) AS 用户标签
FROM users;

核心逻辑：用CONCAT_WS按 “-” 拼接，COALESCE处理性别 NULL 值，YEAR提取注册年份。

五、新手常见误区与避坑指南

误区 1：混淆 “起始位置”（从 0 还是 1 开始）错！标准 SQL 的SUBSTRING起始位置从 1 开始（如提取手机号第 4 位，写SUBSTRING(phone,4,1)），不是编程中的 0 索引，否则会截取错误（如写 3 会拿到第 3 位）。
误区 2：忽略 NULL 值（CONCAT 遇 NULL 返回 NULL）错！CONCAT(username, phone)中，若phone为 NULL，结果就是 NULL。正确做法：用CONCAT_WS自动忽略 NULL，或用COALESCE(phone, '未知')将 NULL 替换为默认值。
误区 3：用 LENGTH 判断中文字符长度错！LENGTH计算字节长度（UTF-8 中 1 个中文 = 3 字节），如 “张三” 的LENGTH是 6，CHAR_LENGTH是 2。判断字符个数必须用CHAR_LENGTH（或 SQL Server 的LEN）。
误区 4：正则表达式语法混用错！不同数据库的正则语法不同（如 MySQL 用REGEXP，PostgreSQL 用~），不要直接复制粘贴，需根据数据库调整。