跳到主要内容

字符串函数与正则表达式

在实际业务中，文本数据处理是非常常见的需求。用户姓名格式化、邮箱验证、日志分析、疾病代码匹配等场景都离不开字符串函数和正则表达式。掌握这些工具，能够让你处理各种文本相关的复杂查询。

字符串函数概览​

常用字符串函数​

函数说明示例
CONCAT(s1, s2, ...)连接字符串CONCAT('Hello', ' ', 'World') → 'Hello World'
CONCAT_WS(sep, s1, s2, ...)用分隔符连接CONCAT_WS('-', '2024', '01', '15') → '2024-01-15'
LENGTH(s)字节数LENGTH('你好') → 6（UTF-8）
CHAR_LENGTH(s)字符数CHAR_LENGTH('你好') → 2
UPPER(s)转大写UPPER('hello') → 'HELLO'
LOWER(s)转小写LOWER('HELLO') → 'hello'
SUBSTRING(s, pos, len)子字符串SUBSTRING('Hello', 2, 3) → 'ell'
LEFT(s, n)左边 n 个字符LEFT('Hello', 2) → 'He'
RIGHT(s, n)右边 n 个字符RIGHT('Hello', 2) → 'lo'
REPLACE(s, old, new)替换REPLACE('Hello', 'l', 'L') → 'HeLLo'
TRIM(s)去两端空格TRIM(' Hello ') → 'Hello'
LPAD(s, len, pad)左填充LPAD('5', 3, '0') → '005'
RPAD(s, len, pad)右填充RPAD('5', 3, '0') → '500'
GROUP_CONCAT(col)组内连接将多行值连接成字符串

LENGTH vs CHAR_LENGTH​

这是处理中文字符时的关键区别：

-- LENGTH：返回字节数
SELECT LENGTH('你好');     -- 结果：6（UTF-8 编码每个中文 3 字节）

-- CHAR_LENGTH：返回字符数
SELECT CHAR_LENGTH('你好'); -- 结果：2

选择建议：

  • 统计"字符数"用 CHAR_LENGTH
  • 统计"存储大小"用 LENGTH

正则表达式​

MySQL 正则语法​

MySQL 使用 REGEXP 或 RLIKE 进行正则匹配：

WHERE column REGEXP 'pattern'
WHERE column RLIKE 'pattern'  -- 同义

常用正则元字符​

元字符说明示例
^字符串开头^Hello 匹配以 Hello 开头
$字符串结尾world$ 匹配以 world 结尾
.任意单个字符H.llo 匹配 Hello, Hxllo 等
*前一个字符出现 0 次或多次ab*c 匹配 ac, abc, abbc 等
+前一个字符出现 1 次或多次ab+c 匹配 abc, abbc 等
?前一个字符出现 0 次或 1 次colou?r 匹配 color, colour
[abc]匹配 a、b 或 c[aeiou] 匹配任意元音
[a-z]匹配 a 到 z[0-9] 匹配任意数字
[^abc]不匹配 a、b、c[^0-9] 匹配非数字
\转义字符\. 匹配点号本身
``或
{n}恰好 n 次a{3} 匹配 aaa
{n,}至少 n 次a{2,} 匹配 aa, aaa, ...
{n,m}n 到 m 次a{1,3} 匹配 a, aa, aaa

正则示例​

-- 以特定字符开头
WHERE name REGEXP '^张'           -- 姓张

-- 以特定字符结尾
WHERE email REGEXP '@gmail\\.com$'  -- Gmail 邮箱

-- 手机号格式（11位数字）
WHERE phone REGEXP '^[0-9]{11}$'

-- 包含特定单词
WHERE conditions REGEXP 'DIAB1'   -- 包含 DIAB1

-- 单词边界匹配
WHERE conditions REGEXP '[[:<:]]DIAB1[[:>:]]'  -- MySQL 单词边界

1667. 修复表中的名字 (Fix Names in a Table)​

题目描述​

Users 表包含用户的 ID 和姓名：

  • user_id: 用户 ID，主键。
  • name: 用户姓名。

请编写一个解决方案，修复 name 字段，使其符合以下格式：

  • 第一个字母大写。
  • 其余字母均为小写。 结果按 user_id 排序。

解题思路​

这道题考查 字符串拆分与重组。

处理步骤：

  1. 提取首字母：SUBSTRING(name, 1, 1) 或 LEFT(name, 1)
  2. 提取剩余部分：SUBSTRING(name, 2)
  3. 首字母转大写：UPPER(...)
  4. 剩余部分转小写：LOWER(...)
  5. 拼接：CONCAT(...)

SUBSTRING 函数说明：

SUBSTRING(string, start, length)
-- start 从 1 开始计数
-- length 可选，省略则取到末尾

SQL 实现 (MySQL)​

SELECT user_id, 
       CONCAT(UPPER(SUBSTRING(name, 1, 1)), LOWER(SUBSTRING(name, 2))) AS name 
FROM Users 
ORDER BY user_id;

-- 或使用 LEFT 函数
SELECT user_id, 
       CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTRING(name, 2))) AS name 
FROM Users 
ORDER BY user_id;

代码解析：

  • SUBSTRING(name, 1, 1)：取第一个字符
  • UPPER(...)：首字母转大写
  • SUBSTRING(name, 2)：从第二个字符开始取到末尾
  • LOWER(...)：剩余部分转小写
  • CONCAT(...)：拼接两部分

1527. 患某种疾病的患者 (Patients With a Condition)​

题目描述​

Patients 表包含患者的 ID、姓名以及患有的疾病代码：

  • patient_id: 患者 ID。
  • patient_name: 患者姓名。
  • conditions: 疾病代码列表（由空格分隔）。

请编写一个解决方案，找出所有患有 I 类糖尿病 (Type I Diabetes) 的患者。该疾病的代码以 DIAB1 开头。

解题思路​

这道题考查 正则表达式的多情况匹配。

关键问题：

目标代码 DIAB1 可能出现在：

  1. 字符串开头：^DIAB1
  2. 空格后面： DIAB1（前面有空格，表示新单词开始）

解决方案：

WHERE conditions REGEXP '^DIAB1| DIAB1'
-- 匹配开头或空格后的 DIAB1

为什么不用 LIKE？

如果用 LIKE '%DIAB1%'，可能会匹配到 SOMEDIAB1 这样的代码，这不是我们想要的。我们需要确保 DIAB1 是一个独立的代码。

SQL 实现 (MySQL)​

SELECT patient_id, patient_name, conditions 
FROM Patients 
WHERE conditions REGEXP '^DIAB1| DIAB1';

代码解析：

  • ^DIAB1：匹配字符串开头
  • DIAB1：匹配空格后（表示新单词）
  • |：或的关系

196. 删除重复的电子邮箱 (Delete Duplicate Emails)​

题目描述​

Person 表包含员工的 ID 和电子邮箱。 请编写一个解决方案，删除 所有重复的电子邮箱，只保留 id 最小的那个。

解题思路​

这道题考查 自连接删除。

删除逻辑：

找出满足以下条件的记录并删除：

  • p1.email = p2.email（邮箱相同）
  • p1.id > p2.id（p1 的 ID 更大，应该删除）

DELETE JOIN 语法：

DELETE t1 FROM table1 t1
JOIN table2 t2 ON condition
WHERE ...

SQL 实现 (MySQL)​

DELETE p1 
FROM Person p1 
JOIN Person p2 ON p1.email = p2.email 
WHERE p1.id > p2.id;

代码解析：

  • DELETE p1：删除 p1 表（别名）中的记录
  • JOIN Person p2：自连接
  • p1.email = p2.email：邮箱相同
  • p1.id > p2.id：p1 的 ID 更大，删除 p1

176. 第二高的薪水 (Second Highest Salary)​

题目描述​

Employee 表记录了员工的薪水信息。 请编写一个解决方案，查询 第二高 的薪水。如果不存在第二高的薪水，则结果应返回 null。

解题思路​

这道题考查 LIMIT OFFSET + 空结果处理。

获取第 N 高的通用方法：

SELECT DISTINCT salary 
FROM Employee 
ORDER BY salary DESC 
LIMIT 1 OFFSET N-1;  -- 第 N 高

为什么需要子查询？

直接使用 LIMIT 时，如果没有第二高，返回空结果集，而不是 NULL。题目要求返回 NULL，所以需要包装成子查询。

MAX 函数的特性：

  • 当输入为空集时，MAX() 返回 NULL
  • 这正好符合题目要求

SQL 实现 (MySQL)​

-- 方法：子查询 + LIMIT OFFSET
SELECT (
    SELECT DISTINCT salary 
    FROM Employee 
    ORDER BY salary DESC 
    LIMIT 1 OFFSET 1
) AS SecondHighestSalary;

代码解析：

  • DISTINCT salary：去重，避免相同薪水
  • ORDER BY salary DESC：降序排列
  • LIMIT 1 OFFSET 1：跳过第一个，取第二个
  • 外层 SELECT：确保空结果返回 NULL

1484. 按日期分组销售产品 (Group Sold Products By The Date)​

题目描述​

Activities 表记录了每天销售的产品名称。 请编写一个解决方案，按 日期 (sell_date) 分组，统计：

  • 销售的不同产品数量 (num_sold)。
  • 这些产品的名称列表 (products)，按字符顺序升序排列并以逗号分隔。

解题思路​

这道题考查 GROUP_CONCAT 函数。

GROUP_CONCAT 语法：

GROUP_CONCAT(
    [DISTINCT] column          -- 去重
    [ORDER BY column ASC/DESC] -- 排序
    [SEPARATOR separator]      -- 分隔符（默认逗号）
)

SQL 实现 (MySQL)​

SELECT sell_date, 
       COUNT(DISTINCT product) AS num_sold, 
       GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS products 
FROM Activities 
GROUP BY sell_date 
ORDER BY sell_date;

代码解析：

  • COUNT(DISTINCT product)：统计不同产品数量
  • GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',')：
    • DISTINCT：去重
    • ORDER BY product：按产品名排序
    • SEPARATOR ','：用逗号分隔

1327. 列出指定时间段内所有的下单产品 (List the Products Ordered in a Period)​

题目描述​

你有两张表：

  • Products: 包含 product_id, product_name, product_category。
  • Orders: 包含 product_id, order_date, unit。

请编写一个解决方案，找出所有在 2020 年 2 月 累计下单数 大于等于 100 的产品名称及其对应的累计下单数量。

解题思路​

这道题考查 JOIN + 分组过滤。

日期筛选：

WHERE order_date LIKE '2020-02-%'  -- 匹配 2020 年 2 月
-- 或
WHERE order_date BETWEEN '2020-02-01' AND '2020-02-29'

SQL 实现 (MySQL)​

SELECT p.product_name, SUM(o.unit) AS unit 
FROM Products p 
JOIN Orders o USING(product_id) 
WHERE o.order_date LIKE '2020-02-%' 
GROUP BY p.product_id 
HAVING unit >= 100;

1517. 查找拥有有效邮箱的用户 (Find Users With Valid E-Mails)​

题目描述​

Users 表记录了用户的信息。

  • user_id: 用户 ID，主键。
  • name: 用户姓名。
  • mail: 用户的邮箱地址。

请编写一个解决方案，找出所有拥有 有效邮箱 的用户。 有效邮箱的规则如下：

  1. 包含名称和域名，域名必须是 @leetcode.com。
  2. 名称 必须以字母开头。
  3. 名称 可以包含字母（大写或小写）、数字、下划线 _、点号 . 或连字号 -。

解题思路​

这道题考查 复杂正则表达式。

正则拆解：

^[A-Za-z][A-Za-z0-9_.-]*@leetcode[.]com$
  • ^：字符串开头
  • [A-Za-z]：第一个字符必须是字母
  • [A-Za-z0-9_.-]*：后续字符可以是字母、数字、下划线、点号、连字号（* 表示 0 次或多次）
  • @leetcode[.]com：固定的域名部分（点号用 [.] 转义）
  • $：字符串结尾

为什么要用 [.] 而不是 \.？

在 MySQL 的 REGEXP 中，点号 . 是特殊字符（匹配任意字符）。要匹配真正的点号，需要用 [.] 或 \\.（双反斜杠）。

SQL 实现 (MySQL)​

SELECT user_id, name, mail 
FROM Users 
WHERE mail REGEXP '^[A-Za-z][A-Za-z0-9_.-]*@leetcode[.]com$';

代码解析：

  • ^[A-Za-z]：以字母开头
  • [A-Za-z0-9_.-]*：名称部分（可以包含字母、数字、_、.、-）
  • @leetcode[.]com$：以 @leetcode.com 结尾

知识点总结​

字符串函数速查​

功能函数
连接CONCAT(), CONCAT_WS()
截取SUBSTRING(), LEFT(), RIGHT()
大小写UPPER(), LOWER()
长度LENGTH(), CHAR_LENGTH()
替换REPLACE()
填充LPAD(), RPAD()
去空格TRIM(), LTRIM(), RTRIM()
组内连接GROUP_CONCAT()

正则表达式要点​

  1. 使用 REGEXP 或 RLIKE 进行匹配
  2. ^ 匹配开头，$ 匹配结尾
  3. [.] 或 \\. 匹配真正的点号
  4. [[:<:]] 和 [[:>:]] 表示单词边界（MySQL 特有）

常见模式​

-- 1. 格式化名称（首字母大写）
CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTRING(name, 2)))

-- 2. 分组内连接
GROUP_CONCAT(DISTINCT col ORDER BY col SEPARATOR ',')

-- 3. 正则匹配邮箱
WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'

-- 4. 第 N 高的值
SELECT (SELECT DISTINCT col FROM table ORDER BY col DESC LIMIT 1 OFFSET N-1)