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

String str="CLARK,JONES,MARTIN";
String sql="select * from emp where ename in("+str+")";
1
2
你要是用下面这种方式写，那可能会被问候了：

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
怎么做？？？
这么做！
分两步：

把ename拆分为单个字母显示。
把多行数据合并为一行显示。
下面先处理一行数据

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


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

免责申明：

本文系转载，版权归原作者所有，如若侵权请联系我们进行删除！

想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友，浏览袋鼠云官网：https://www.dtstack.com/?src=bbs
同时，欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」，交流最新开源技术信息，群号码：30537511，项目地址：https://github.com/DTStack