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

embedded/2024/12/28 21:35:14/

在数据分析和开发过程中,原始数据往往存在格式不统一、冗余字符等问题,直接影响查询和展示效果。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
idnameemail
1aliceALICE@EXAMPLE.COM
2BOBbob@example.com
3cHarLieCHARLIE@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;  

查询结果
idformatted_nameformatted_email
1Alicealice@example.com
2Bobbob@example.com
3Charliecharlie@example.com

解释

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

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

需求

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

表结构 orders
order_idcustomer_namephone
101John Doe138-1234-5678
102Jane Smith139 1234 5678
103Alice18812345678

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

查询结果
order_idcustomer_namecleaned_phone
101John Doe13812345678
102Jane Smith13912345678
103Alice18812345678

解释

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

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

需求

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

表结构 customers
idnameaddress
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;  

查询结果
idnameprovincecity
1李明广东省深圳市
2王强浙江省杭州市
3张伟北京市朝阳区

解释

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

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

需求

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

表结构 comments
iduser_namecontent
1小张这服务太差了
2小李这个产品垃圾
3小王物流速度慢死了

SQL 实现
sql">-- 敏感词替换
SELECT  id,  user_name,  REPLACE(content, '垃圾', '***') AS filtered_content  
FROM comments;  

查询结果
iduser_namefiltered_content
1小张这服务太差了
2小李这个产品***
3小王物流速度慢死了

总结

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

http://www.ppmy.cn/embedded/149561.html

相关文章

Linux下Java通过JNI调用C++

以下为Demo流程 1.创建Java文件 public class HelloWord {// 声明本地方法public native void sayHello();static {// 加载本地库System.loadLibrary("hello");}public static void main(String[] args) {new HelloWord().sayHello();} } 2.编译生成.h头文件 在H…

5.npm包

文章目录 [TOC](文章目录) 3.npm与包3.1.包3.2.npm体验在项目中安装包的命令包管理配置文件一次性安装开发项目时安装的包如何从项目中卸载包devDependencies节点的作用解决下载包速度比较慢的问题nrm工具,利用其提供的终端命令,可以快速查看和切换下包的…

iPhone 17 :史诗级大改,120Hz 全面普及

资深果粉应该都听过一个说法:“iPhone 买单不买双”。这个“规律”似乎在iPhone 16上也得到了印证。 近段时间,各方消息都在指明一点:iPhone 16 只是大餐前的小菜,iPhone 17才是真正带来革命性提升的一代神机。下一代 iPhone 17&…

阿里云人工智能ACP(一)——人工智能与人工智能技术概述

一、人工智能概述 1. 人工智能的定义 人工智能 是利用数字计算机或者由数字计算机控制的机器,模拟、延伸和扩展人类的智能,感知环境、获取知识并使用知识获得最佳结果的理论、方法、技术和应用系统。 2. 人工智能的发展 3. 人工智能的分类 4. 人工智能…

网站前端优化

网站前端优化 主要是介绍了这几个规则。 第一:减少HTTP请求 1: 将超链接关联到图片上,例如在导航栏按钮中。如果是以这种形式关联多个带有超链接的图片,使用图片地图这种方式既能减少HTTP请求,有无需改变页面外观感受。图片地…

Vue.js 高级组件开发:抽象与高性能架构

Vue.js 高级组件开发:抽象与高性能架构 引言一、动态组件与依赖注入1. 动态组件场景 二、高可扩展性的抽象组件模式1. 设计思路2. 案例:抽象数据表组件 三、复杂场景下的异步操作管理1. 使用 Vue Composition API 管理异步逻辑 四、渲染优化与框架底层钩…

微信小程序 不同角色进入不同页面、呈现不同底部导航栏

遇到这个需求之前一直使用的小程序默认底部导航栏,且小程序默认入口页面为pages/index/index,要使不同角色呈现不同底部导航栏,必须要在不同页面引用不同的自定义导航栏。本篇将结合分包(subPackages)展开以下三步叙述…

202年寒假充电计划——自学手册 网络安全(黑客技术)

🤟 基于入门网络安全/黑客打造的:👉黑客&网络安全入门&进阶学习资源包 前言 什么是网络安全 网络安全可以基于攻击和防御视角来分类,我们经常听到的 “红队”、“渗透测试” 等就是研究攻击技术,而“蓝队”、…