系列文章目录

【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是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行

---

文章目录

• 系列文章目录
• 前言
• 一、将分隔数据转换为多值IN列表
• 二、按字母顺序排列字符串
• 三、去重后按字母顺序排列字符串
• 四、如何删除字符串中的字符保留数字
• 总结

---

前言

本篇文章讲解的主要内容是：如何将分隔数据转换为多值IN列表、如何按字母顺序排列字符串、如何对字符串字母去重后按字母顺序排列字符串、如何删除字符串中的字符保留数字。
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

---

一、将分隔数据转换为多值IN列表

工作中经常有这么一个场景，用户通过前端页面传入了一个字符串列表如：('CLARK,JONES,MARTIN'),要求根据这个串查询相关用户信息。
开发人员需要你提供一个sql他嵌套到代码里面，想直接把这个字符串传给这个sql然后做查询，java代码如下，你会怎么写？

String str="CLARK,JONES,MARTIN";
String sql="select * from emp where ename in("+str+")";

你要是用下面这种方式写，那可能会被问候了：

SQL> select * from emp where ename in('CLARK,JONES,MARTIN');

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------

SQL> 

直接把'CLARK,JONES,MARTIN'带入肯定是查询不到数据的。
那该怎么实现？？？🤔🤔🤔
我们需要做个转换。把前端传过来的'CLARK,JONES,MARTIN'改写成'CLARK','JONES','MARTIN'然后再查询就能查到数据了，
接下来还是用正则来做这个需求，正则在这块有先天优势！下面是我模拟的上面java代码传值的过程：

SQL> var v_name varchar2;

SQL> exec :v_name:='CLARK,JONES,MARTIN';

PL/SQL procedure successfully completed

v_name
---------
CLARK,JONES,MARTIN

SQL> select *
  2    from emp
  3   where ename in (select regexp_substr(:v_name, '[^,]+' ,1, level)
  4                     from dual
  5                   connect by level <= regexp_count(:v_name,',')+1);

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
v_name
---------
CLARK,JONES,MARTIN

为了便于理解，我拿出来内部的查询结果以及对应伪列level给大家看看

SQL> select regexp_substr(:v_name, '[^,]+' ,1, level)as userss,level
  2                     from dual
  3                   connect by level <= regexp_count(:v_name,',')+1;

USERSS                                                                                LEVEL
-------------------------------------------------------------------------------- ----------
CLARK                                                                                     1
JONES                                                                                     2
MARTIN                                                                                    3

v_name
---------
CLARK,JONES,MARTIN

每行对应的正则表达式'[^,]+'表示对应一个不包含逗号的字符串，最后一个参数表示分别取第1、2、3三个串。
那么结合这个语句就可以达到需求。从上面结果看到，原来v_name='CLARK,JONES,MARTIN'是一个字符串，而现在变成了三行了，也就是对应'CLARK','JONES','MARTIN'了
这样子in('CLARK','JONES','MARTIN')就能正常查询到结果了！

二、按字母顺序排列字符串

我现在有个需求，想把emp表中的ename名称，按照字母顺序排序！！！如ADAMS->AADMS
怎么做？？？
这么做！
分两步：

1. 把ename拆分为单个字母显示。
2. 把多行数据合并为一行显示。

下面先处理一行数据

• 1、拆分

SQL> select 'ADAMS' as sour,regexp_count('ADAMS','[[:alpha:]]') as lev, regexp_substr('ADAMS','[[:alpha:]]',1,level) as str
  2  from dual
  3  connect by level<=regexp_count('ADAMS','[[:alpha:]]')
  4  ;

SOUR                                    LEV STR
-------------------------------- ---------- ----------------------------------------------------------------
ADAMS                                     5 A
ADAMS                                     5 D
ADAMS                                     5 A
ADAMS                                     5 M
ADAMS                                     5 S

• 2、用listagg合并

SQL> with t as
  2   (select 'ADAMS' as sour,
  3           regexp_count('ADAMS', '[[:alpha:]]') as lev,
  4           regexp_substr('ADAMS', '[[:alpha:]]', 1, level) as str
  5      from dual
  6    connect by level <= regexp_count('ADAMS', '[[:alpha:]]')
  7    )
  8  select sour, listagg(str) within group(order by str) as heb
  9    from t
 10   group by sour;

SOUR                             HEB
-------------------------------- --------------------------------------------------------------------------------
ADAMS                            AADMS

这一步操作内容大家应该都能看明白了，那接下来的话，咱们处理全表的数据！

• 3、处理全表数据

下面进一步处理全表数据，可以把前面的语句改为标量子查询：

SQL> select ename,
  2         (select listagg(substr(ename, level, 1)) within group(order by substr(ename, level, 1))
  3            from dual
  4          connect by level <= length(ename)) as sort_ename
  5    from emp;

ENAME      SORT_ENAME
---------- --------------------------------------------------------------------------------
SMITH      HIMST
ALLEN      AELLN
WARD       ADRW
JONES      EJNOS
MARTIN     AIMNRT
BLAKE      ABEKL
CLARK      ACKLR
SCOTT      COSTT
KING       GIKN
TURNER     ENRRTU
ADAMS      AADMS
JAMES      AEJMS
FORD       DFOR
MILLER     EILLMR
test       estt

15 rows selected

或许有人会注意到，在上面的数据中有很多字母是重复的，如我们举例用的字符串ADAMS->AADMS,排序后就有两个"A"。对这种数据，如果要去重怎么办？

三、去重后按字母顺序排列字符串

我们在标量子查询里加一个group by即可（注意：把"substr(ename,LEVEL,1)"当作一个整体比较容易理解）。

SQL> select ename,
  2         (select listagg(min(substr(ename, level, 1))) within group(order by min(substr(ename, level, 1)))
  3            from dual
  4          connect by level <= length(ename)
  5           group by substr(ename, level, 1)) as sort_ename
  6    from emp;

ENAME      SORT_ENAME
---------- --------------------------------------------------------------------------------
SMITH      HIMST
ALLEN      AELN
WARD       ADRW
JONES      EJNOS
MARTIN     AIMNRT
BLAKE      ABEKL
CLARK      ACKLR
SCOTT      COST
KING       GIKN
TURNER     ENRTU
ADAMS      ADMS
JAMES      AEJMS
FORD       DFOR
MILLER     EILMR
test       est

15 rows selected

如此我们就实现了去重后再排序的需求

四、如何删除字符串中的字符保留数字

用人话说就是想找出来带数值的数据并清理掉非数字字符，
有这么一个临时表

SQL> select to_char(empno) from emp where deptno=10
  2  union all
  3  select dname||deptno from dept
  4  union all
  5  select ename from emp where deptno=20;

TO_CHAR(EMPNO)
------------------------------------------------------
7782
7839
7934
ACCOUNTING10
RESEARCH20
SALES30
OPERATIONS40
SMITH
JONES
SCOTT
ADAMS
FORD

12 rows selected

我现在有个需求，想找一下哪些数据是包含数字的。
那这个需求怎么实现起来简单呢？
这么做：
先正则替换掉所有非数字字符，然后外层嵌套一层找非空行就可以了！

SQL> with t as
  2   (select to_char(empno) as aa
  3      from emp
  4     where deptno = 10
  5    union all
  6    select dname || deptno
  7      from dept
  8    union all
  9    select ename
 10      from emp
 11     where deptno = 20)
 12  select *
 13    from (select aa, regexp_replace(aa, '[^[:digit:]]+') as nub from t)
 14   where nub is not null;

AA                                                     NUB
------------------------------------------------------ --------------------------------------------------------------------------------
7782                                                   7782
7839                                                   7839
7934                                                   7934
ACCOUNTING10                                           10
RESEARCH20                                             20
SALES30                                                30
OPERATIONS40                                           40

7 rows selected

当然了，你也可以用translate来实现：

SQL> with t as
  2   (select to_char(empno) as aa
  3      from emp
  4     where deptno = 10
  5    union all
  6    select dname || deptno
  7      from dept
  8    union all
  9    select ename
 10      from emp
 11     where deptno = 20)
 12  select *
 13    from (
 14    select translate(aa,'0123456789'||aa,'0123456789') nub
 15    from t
 16    )
 17   where nub is not null;

NUB
--------------------------------------------------------------------------------
7782
7839
7934
10
20
30
40

7 rows selected

我这里写的都是简单写法，麻烦的也有，我就不写了，浪费时间不说，我还怕有人被我带偏了嘿嘿
🤣🤣🤣

---

总结

这篇文章还是介绍的字符串处理案例，还是那句话，这些操作太太太常见了。后面还会写，而且是更麻烦更难的需求。