系列文章目录
【SQL开发实战技巧】系列（一）:关于SQL不得不说的那些事
【SQL开发实战技巧】系列（二）：简单单表查询
【SQL开发实战技巧】系列（三）：SQL排序的那些事
【SQL开发实战技巧】系列（四）：从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项
【SQL开发实战技巧】系列（五）：从执行计划看IN、EXISTS 和 INNER JOIN效率，我们要分场景不要死记网上结论
【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放
【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起
【SQL开发实战技巧】系列（八）：聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表
【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！
【SQL开发实战技巧】系列（十）：从拆分字符串、替换字符串以及统计字符串出现次数说起
【SQL开发实战技巧】系列（十一）：拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数
【SQL开发实战技巧】系列（十二）：三问(如何对字符串字母去重后按字母顺序排列字符串？如何识别哪些字符串中包含数字？如何将分隔数据转换为多值IN列表?)
【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加
【SQL开发实战技巧】系列（十四）：计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工
【SQL开发实战技巧】系列（十五）：查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算
【SQL开发实战技巧】系列（十七）：数据仓库中时间类型操作（初级）确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
【SQL开发实战技巧】系列（十八）：数据仓库中时间类型操作（进阶）INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算
【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？
【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据
【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据
【SQL开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式
【SQL开发实战技巧】系列（二十三）：数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高
【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过案例执行计划详解”行转列”,”列转行”是如何实现的
【SQL开发实战技巧】系列（二十五）：数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组
【SQL开发实战技巧】系列（二十六）：数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行
【SQL开发实战技巧】系列（二十七）：数仓报表场景☞通过对移动范围进行聚集来详解分析函数开窗原理以及如何一个SQL打印九九乘法表
【SQL开发实战技巧】系列（二十八）：数仓报表场景☞人员分布问题以及不同组(分区)同时聚集如何实现
【SQL开发实战技巧】系列（二十九）：数仓报表场景☞简单的树形(分层)查询以及如何确定根节点、分支节点和叶子节点
【SQL开发实战技巧】系列（三十）：数仓报表场景☞树形(分层)查询如何排序？以及如何在树形查询中正确的使用where条件
【SQL开发实战技巧】系列（三十一）：数仓报表场景☞分层查询如何只查询树形结构某一个分支？如何剪掉一个分支？
【SQL开发实战技巧】系列（三十二）：数仓报表场景☞对表中某个字段内的值去重
【SQL开发实战技巧】系列（三十三）：数仓报表场景☞从不固定位置提取字符串的元素以及搜索满足字母在前数字在后等条件的数据
文章目录
- 系列文章目录
- 前言
- 一、从不固定位置提取字符串的元素
- 二、搜索字母数字混合的字符串
- 总结
前言
本篇文章讲解的主要内容是：通过需求：从不固定位置提取字符串的元素，要求取出方括号中对应的字符串，分三列显示。给大家介绍regexp_substr正则表达式日常工作中的巧妙用法。通过需求：搜索同时包含字母数字混合的字符串，要求返回T中既包含字母，又包含数字的行。那么只要其中有”字母在前，数字在后”或”数字在前，字母在后”的数据均可需求，介绍regexp_like的使用场景。
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
一、从不固定位置提取字符串的元素
有下面测试数据：
with t as
(SELECT 'xxxxxabc[867]xxx[-]xxxx[5309]xxxxx' msg
FROM dual
union all
select 'FEDERAL ID#[zhao]45 3972708 MR [yan]GARY HANSON 25 SHORELINE DR DANA POINT CA 92629[dong]'
from dual
UNION ALL
SELECT 'xxxxxtime:[11271978]favnum:[4]id:[Joe]xxxxx' msg
FROM dual
UNION all
SELECT 'call:[F_GET_ROWS()]bl:[ROSEWOOD...SIR]b2:[44400002]77.90xxxxx' msg
FROM dual)
select * from t;
MSG
--------------------------------------------------------------------------------
xxxxxabc[867]xxx[-]xxxx[5309]xxxxx
FEDERAL ID#[zhao]45 3972708 MR [yan]GARY HANSON 25 SHORELINE DR DANA POINT CA 92
xxxxxtime:[11271978]favnum:[4]id:[Joe]xxxxx
call:[F_GET_ROWS()]bl:[ROSEWOOD...SIR]b2:[44400002]77.90xxxxx
现在有个需求：要求取出方括号中对应的字符串，分三列显示。
我们来分析一下，这里的分隔符是两个’[’,’]’,按前面文章我讲的方法，这个需求我们可以实现为：
with t as
(SELECT 'xxxxxabc[867]xxx[-]xxxx[5309]xxxxx' msg
FROM dual
union all
select 'FEDERAL ID#[zhao]45 3972708 MR [yan]GARY HANSON 25 SHORELINE DR DANA POINT CA 92629[dong]'
from dual
UNION ALL
SELECT 'xxxxxtime:[11271978]favnum:[4]id:[Joe]xxxxx' msg
FROM dual
UNION all
SELECT 'call:[F_GET_ROWS()]bl:[ROSEWOOD...SIR]b2:[44400002]77.90xxxxx' msg
FROM dual)
select
regexp_substr(msg,'[^][]+',1,2) as ones,
regexp_substr(msg,'[^][]+',1,4) as twos,
regexp_substr(msg,'[^][]+',1,6) as thrs
from t;
ONES TWOS THRS
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
867 - 5309
zhao yan dong
11271978 4 Joe
F_GET_ROWS() ROSEWOOD...SIR 44400002
看上面结果，我们是实现了这个需求的。但是别高兴太早了，如果增加一行下面这种数据呢？
with t as
(SELECT 'xxxxxabc[867]xxx[-]xxxx[5309]xxxxx' msg
FROM dual
union all
select 'FEDERAL ID#[zhao]45 3972708 MR [yan]GARY HANSON 25 SHORELINE DR DANA POINT CA 92629[dong]'
from dual
UNION ALL
SELECT 'xxxxxtime:[11271978]favnum:[4]id:[Joe]xxxxx' msg
FROM dual
UNION all
SELECT 'call:[F_GET_ROWS()]bl:[ROSEWOOD...SIR]b2:[44400002]77.90xxxxx' msg
FROM dual union all
select '[zhao][y][d]' from dual)
select
regexp_substr(msg,'[^][]+',1,2) as ones,
regexp_substr(msg,'[^][]+',1,4) as twos,
regexp_substr(msg,'[^][]+',1,6) as thrs
from t;
ONES TWOS THRS
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
867 - 5309
zhao yan dong
11271978 4 Joe
F_GET_ROWS() ROSEWOOD...SIR 44400002
y
诶嘿，明明新加的'[zhao][y][d]'这条数据的'zhao y d'也是符合要求的，上面查询为啥就只查出来了个’y’啊?这样查有问题啊！！！
通过上面问题，我们抛出来个问题：如何更准确的定位？
其实我们可以考虑，把包含方括号在内的数据一起取出来，然后再去除方括号！那么解决方案如下：
先查出来含方括号的数据：
with t as
(SELECT 'xxxxxabc[867]xxx[-]xxxx[5309]xxxxx' msg
FROM dual
union all
select 'FEDERAL ID#[zhao]45 3972708 MR [yan]GARY HANSON 25 SHORELINE DR DANA POINT CA 92629[dong]'
from dual
UNION ALL
SELECT 'xxxxxtime:[11271978]favnum:[4]id:[Joe]xxxxx' msg
FROM dual
UNION all
SELECT 'call:[F_GET_ROWS()]bl:[ROSEWOOD...SIR]b2:[44400002]77.90xxxxx' msg
FROM dual union all
select '[zhao][y][d]' from dual)
select
regexp_substr(msg,'([)([^]]+)',1,1) as ones,
regexp_substr(msg,'([)([^]]+)',1,2) as twos,
regexp_substr(msg,'([)([^]]+)',1,3) as thrs
from t;
ONES TWOS THRS
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
[867 [- [5309
[zhao [yan [dong
[11271978 [4 [Joe
[F_GET_ROWS() [ROSEWOOD...SIR [44400002
[zhao [y [d
这里"([)",中的斜杠是转义字符，"([)"放在括号里是为了便于区分和理解。
"([^]]+)"就是用”]”作为分隔符的一个正则表达式，同样，"[^]]+“放在括号里是为了便于区分和理解。
上面查询语句的查询结果，和我们的需求基本一致了，但是还有个”[“要去掉，那么再进行一次ltrim就好啦：
with t as
(SELECT 'xxxxxabc[867]xxx[-]xxxx[5309]xxxxx' msg
FROM dual
union all
select 'FEDERAL ID#[zhao]45 3972708 MR [yan]GARY HANSON 25 SHORELINE DR DANA POINT CA 92629[dong]'
from dual
UNION ALL
SELECT 'xxxxxtime:[11271978]favnum:[4]id:[Joe]xxxxx' msg
FROM dual
UNION all
SELECT 'call:[F_GET_ROWS()]bl:[ROSEWOOD...SIR]b2:[44400002]77.90xxxxx' msg
FROM dual union all
select '[zhao][y][d]' from dual)
select
ltrim(regexp_substr(msg,'([)([^]]+)',1,1),'[') as ones,
ltrim(regexp_substr(msg,'([)([^]]+)',1,2),'[') as twos,
ltrim(regexp_substr(msg,'([)([^]]+)',1,3),'[') as thrs
from t;
ONES TWOS THRS
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
867 - 5309
zhao yan dong
11271978 4 Joe
F_GET_ROWS() ROSEWOOD...SIR 44400002
zhao y d
二、搜索字母数字混合的字符串
现有如下数据：
with t as (
select 'zhaoyandong' as str from dual union all
select '345345zhaoyd' as str from dual union all
select 'zyd43223' as str from dual union all
select '23423' as str from dual union all
select '1000' as str from dual
)
select * from t;
STR
------------
zhaoyandong
345345zhaoyd
zyd43223
23423
1000
现在有个需求：要求返回T中既包含字母，又包含数字的行。
那么只要其中有”字母在前，数字在后”或”数字在前，字母在后”的数据均可。
那么我们先来查出来”字母在前，数字在后”的数据：
with t as (
select 'zhaoyandong' as str from dual union all
select '345345zhaoyd' as str from dual union all
select 'zyd43223' as str from dual union all
select '23423' as str from dual union all
select '1000' as str from dual
)
select * from t where regexp_like(str,'^([[:alpha:]]+).*([[:digit:]]+)$');
STR
------------
zyd43223
再查出来”数字在前，字母在后”的数据：
with t as (
select 'zhaoyandong' as str from dual union all
select '345345zhaoyd' as str from dual union all
select 'zyd43223' as str from dual union all
select '23423' as str from dual union all
select '1000' as str from dual
)
select * from t where regexp_like(str,'^([[:digit:]]+).*([[:alpha:]]+)$');
STR
------------
345345zhaoyd
二者结合一下就可以啦，如下查询：
with t as (
select 'zhaoyandong' as str from dual union all
select '345345zhaoyd' as str from dual union all
select 'zyd43223' as str from dual union all
select '23423' as str from dual union all
select '1000' as str from dual
)
select * from t where regexp_like(str,'^([[:alpha:]]+).*([[:digit:]]+)$|^([[:digit:]]+).*([[:alpha:]]+)$');
STR
------------
345345zhaoyd
zyd43223
‘|’是或则的意思！
总结
本篇文章讲解的主要内容是：通过需求：从不固定位置提取字符串的元素，要求取出方括号中对应的字符串，分三列显示。给大家介绍regexp_substr正则表达式日常工作中的巧妙用法。通过需求：搜索同时包含字母数字混合的字符串，要求返回T中既包含字母，又包含数字的行。那么只要其中有”字母在前，数字在后”或”数字在前，字母在后”的数据均可需求，介绍regexp_like的使用场景。
