SQL 实战：字符串处理函数 – 数据清洗与文本格式化

在数据分析和开发过程中，原始数据往往存在格式不统一、冗余字符等问题，直接影响查询和展示效果。SQL 提供了一系列强大的字符串处理函数，能够帮助开发者进行数据清洗和文本格式化操作，提高数据质量和查询效率。本文将通过多个实战案例，详细讲解 SQL 中常用的字符串处理函数及其应用场景。

一、常用字符串函数概览

二、实战案例：数据清洗与文本格式化

案例 1：统一用户输入数据格式（大小写转换）

需求

在用户注册系统中，部分用户姓名或邮箱输入存在大小写不一致的问题，需要将所有邮箱地址转换为小写，姓名转换为首字母大写格式。

表结构 users

SQL 实现

-- 将邮箱地址统一转换为小写，姓名首字母大写
SELECT  
    id,  
    CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTRING(name, 2))) AS formatted_name,  
    LOWER(email) AS formatted_email  
FROM users;  

查询结果

解释：

• UPPER(LEFT(name, 1)) 取得姓名的首字母并转换为大写。
• LOWER(SUBSTRING(name, 2)) 取得姓名剩余部分并转换为小写。
• LOWER(email) 直接将邮箱地址转换为小写，保持一致性。

案例 2：去除冗余空格与格式化手机号

需求

在订单系统中，用户手机号存在不规范输入，如前后多余空格、间隔符等。需要清洗手机号数据，使其保持一致格式。

表结构 orders

SQL 实现

-- 去除手机号空格和特殊字符，格式化为纯数字
SELECT  
    order_id,  
    customer_name,  
    REPLACE(REPLACE(TRIM(phone), '-', ''), ' ', '') AS cleaned_phone  
FROM orders;  

查询结果

解释：

• TRIM(phone) 去除手机号前后空格。
• REPLACE(phone, '-', '') 去掉短横线，REPLACE(..., ' ', '') 去掉空格，实现纯数字格式。

案例 3：从地址中提取城市与省份

需求

在客户表中，地址字段格式为省-市-区，需要提取省份和城市信息进行分列存储。

表结构 customers

SQL 实现

-- 提取省份和城市
SELECT  
    id,  
    name,  
    SUBSTRING_INDEX(address, '-', 1) AS province,  
    SUBSTRING_INDEX(SUBSTRING_INDEX(address, '-', 2), '-', -1) AS city  
FROM customers;  

查询结果

解释：

• SUBSTRING_INDEX(address, '-', 1) 提取第一个 - 之前的字符串（省份）。
• SUBSTRING_INDEX(SUBSTRING_INDEX(address, '-', 2), '-', -1) 先提取前两部分，再从后往前截取城市信息。

案例 4：用户评论敏感词替换

需求

在评论系统中，用户评论可能包含敏感词，需要将敏感词替换为 *** 进行过滤。

表结构 comments

SQL 实现

-- 敏感词替换
SELECT  
    id,  
    user_name,  
    REPLACE(content, '垃圾', '***') AS filtered_content  
FROM comments;  

查询结果

总结

• 字符串处理函数在 SQL 查询中广泛应用于数据清洗、格式化和提取。
• 使用 TRIM()、REPLACE()、SUBSTRING() 等函数能够有效解决数据不规范问题，提高数据分析的准确性。
• 在复杂数据处理中，通过组合使用多种字符串函数，可以减少应用层逻辑，实现高效的数据操作和清洗。