在数据分析和开发过程中,原始数据往往存在格式不统一、冗余字符等问题,直接影响查询和展示效果。SQL 提供了一系列强大的字符串处理函数,能够帮助开发者进行数据清洗和文本格式化操作,提高数据质量和查询效率。本文将通过多个实战案例,详细讲解 SQL 中常用的字符串处理函数及其应用场景。
一、常用字符串函数概览
函数名 说明 示例 UPPER() / LOWER() 转换为大写 / 小写 UPPER('sql ')
→ SQL
TRIM() 去除字符串前后空格 TRIM(' hello ')
→ hello
LTRIM() / RTRIM() 去除左 / 右侧空格 LTRIM(' hello')
→ hello
SUBSTRING() 截取子字符串 SUBSTRING('hello', 2, 3)
→ ell
LEFT() / RIGHT() 从左 / 右截取指定长度的字符串 LEFT('hello', 3)
→ hel
REPLACE() 替换字符串中的子串 REPLACE('hello', 'l', 'p')
→ heppo
CONCAT() 拼接字符串 CONCAT('SQL', ' is fun')
→ SQL is fun
CHAR_LENGTH() 计算字符串长度 CHAR_LENGTH('hello')
→ 5
INSTR() 返回子串首次出现的位置 INSTR('hello', 'l')
→ 3
LOCATE() 查找子串位置(与 INSTR 类似) LOCATE('l', 'hello')
→ 3
REPEAT() 重复字符串 REPEAT('ha', 3)
→ hahaha
REVERSE() 反转字符串 REVERSE('sql ')
→ lqs
LPAD() / RPAD() 左 / 右填充字符串 LPAD('5', 3, '0')
→ 005
FORMAT() 格式化数字并保留小数位 FORMAT(1234.56, 2)
→ 1,234.56
二、实战案例:数据清洗与文本格式化
案例 1:统一用户输入数据格式(大小写转换)
需求
在用户注册系统中,部分用户姓名或邮箱输入存在大小写不一致的问题,需要将所有邮箱地址转换为小写,姓名转换为首字母大写格式。
表结构 users
id name email 1 alice ALICE@EXAMPLE.COM 2 BOB bob@example.com 3 cHarLie CHARLIE@EXAMPLE.COM
SQL 实现
sql">
SELECT id, CONCAT( UPPER( LEFT ( name, 1 ) ) , LOWER( SUBSTRING( name, 2 ) ) ) AS formatted_name, LOWER( email) AS formatted_email
FROM users;
查询结果
id formatted_name formatted_email 1 Alice alice@example.com 2 Bob bob@example.com 3 Charlie charlie@example.com
解释 :
UPPER(LEFT(name, 1))
取得姓名的首字母并转换为大写。LOWER(SUBSTRING(name, 2))
取得姓名剩余部分并转换为小写。LOWER(email)
直接将邮箱地址转换为小写,保持一致性。
案例 2:去除冗余空格与格式化手机号
需求
在订单系统中,用户手机号存在不规范输入,如前后多余空格、间隔符等。需要清洗手机号数据,使其保持一致格式。
表结构 orders
order_id customer_name phone 101 John Doe 138-1234-5678 102 Jane Smith 139 1234 5678 103 Alice 18812345678
SQL 实现
sql">
SELECT order_id, customer_name, REPLACE ( REPLACE ( TRIM( phone) , '-' , '' ) , ' ' , '' ) AS cleaned_phone
FROM orders;
查询结果
order_id customer_name cleaned_phone 101 John Doe 13812345678 102 Jane Smith 13912345678 103 Alice 18812345678
解释 :
TRIM(phone)
去除手机号前后空格。REPLACE(phone, '-', '')
去掉短横线,REPLACE(..., ' ', '')
去掉空格,实现纯数字格式。
案例 3:从地址中提取城市与省份
需求
在客户表中,地址字段格式为省-市-区
,需要提取省份和城市信息进行分列存储。
表结构 customers
id name address 1 李明 广东省-深圳市-南山区 2 王强 浙江省-杭州市-西湖区 3 张伟 北京市-朝阳区
SQL 实现
sql">
SELECT id, name, SUBSTRING_INDEX( address, '-' , 1 ) AS province, SUBSTRING_INDEX( SUBSTRING_INDEX( address, '-' , 2 ) , '-' , - 1 ) AS city
FROM customers;
查询结果
id name province city 1 李明 广东省 深圳市 2 王强 浙江省 杭州市 3 张伟 北京市 朝阳区
解释 :
SUBSTRING_INDEX(address, '-', 1)
提取第一个 -
之前的字符串(省份)。SUBSTRING_INDEX(SUBSTRING_INDEX(address, '-', 2), '-', -1)
先提取前两部分,再从后往前截取城市信息。
案例 4:用户评论敏感词替换
需求
在评论系统中,用户评论可能包含敏感词,需要将敏感词替换为 ***
进行过滤。
表结构 comments
id user_name content 1 小张 这服务太差了 2 小李 这个产品垃圾 3 小王 物流速度慢死了
SQL 实现
sql">
SELECT id, user_name, REPLACE ( content, '垃圾' , '***' ) AS filtered_content
FROM comments;
查询结果
id user_name filtered_content 1 小张 这服务太差了 2 小李 这个产品*** 3 小王 物流速度慢死了
总结
字符串处理函数 在 SQL 查询中广泛应用于数据清洗、格式化和提取。使用 TRIM()、REPLACE()、SUBSTRING() 等函数 能够有效解决数据不规范问题,提高数据分析的准确性。 在复杂数据处理中,通过组合使用多种字符串函数,可以减少应用层逻辑,实现高效的数据操作和清洗。