【SQL开发实战技巧】系列（十一）：拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数

赵延东的一亩三分地 2023-04-15 原文

系列文章目录

文章目录

前言

本篇文章讲解的主要内容是：如何使用translate或regexp_replace提取姓名的大写首字母缩写、如何使用translate或regexp_replace按字符串中的数值排序、如何聚合表中的行创建一个以逗号分隔拼接的字符串(函数LISTAGG、wmsys.wm_concat)、如何使用substr或regexp_substr提取第N个分隔符的子串、如何分解IP地址。
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

一、如何使用translate或regexp_replace提取姓名的大写首字母缩写

现在有一个需求：将下面临时表中的首大写字母，中间加".“显示为"Z.Y.D”:

with t as(
select 'Zhao Yan Dong' as aa from dual
)

我们可以利用regexp_replace的分组替换功能：

with t as(
select 'Zhao Yan Dong' as aa from dual
)
select regexp_replace(aa,'([[:upper:]])(.*)([[:upper:]])(.*)([[:upper:]])(.*)','\1.\3.\5') from t;
REGEXP_REPLACE(AA,'([[:UPPER:]])(.*)([[:UPPER:]])(.*)([[:UPPER:]])(.*)','\1.\3.\
--------------------------------------------------------------------------------
Z.Y.D

括号()将子表达式分组为一个替换单元、量词单元或后向引用单元。
在这个查询中，我们用()把符串分成了6个组，其中第1、3、5组中是大写字母，然后通过后向引用'\1.\3.\5'就分别取到了3个组的大写字母，并在中间增加了字符’.'。
也可以用前面介绍的translate函数。

SQL> 
SQL> with t as(
  2  select 'Zhao Yan Dong' as aa from dual
  3  )
  4  select translate(aa,1||' '||lower(aa),'1.') from t;

TRANSLATE(AA,1||''||LOWER(AA),'1.')
----------------------------------------------------------------
Z.Y.D

这个逻辑大家应该可以看得明白！

二、如何实现按字符串中的数字或字母排序

有下面一个临时表：

SQL> 
SQL> with t as (
  2  select dname||deptno||loc as aa
  3  from dept
  4  )
  5  select * from t;

AA
-------------------------------------------------------------------
ACCOUNTING10NEW YORK
RESEARCH20DALLAS
SALES30CHICAGO
OPERATIONS40BOSTON

现在有个需求：想根据aa字段中的数值去排序！那么怎么做？
我们可以用正则表达式替换非数字字符，语句如下：

with t as
 (select dname || deptno || loc as aa from dept)
select regexp_replace(aa, '[^[:digit:]]') as newaa, aa from t order by 1;

NEWAA                                                                            AA
-------------------------------------------------------------------------------- -------------------------------------------------------------------
10                                                                               ACCOUNTING10NEW YORK
20                                                                               RESEARCH20DALLAS
30                                                                               SALES30CHICAGO
40                                                                               OPERATIONS40BOSTON

也可以用translate函数，直接替换掉非数字字符：

SQL> with t as
  2   (select dname || deptno || loc as aa from dept)
  3  select translate(aa,'0123456789'||aa,'0123456789') as newaa, aa from t order by 1;

NEWAA                                                                            AA
-------------------------------------------------------------------------------- -------------------------------------------------------------------
10                                                                               ACCOUNTING10NEW YORK
20                                                                               RESEARCH20DALLAS
30                                                                               SALES30CHICAGO
40                                                                               OPERATIONS40BOSTON

三、如何聚合表中的行创建一个以逗号分隔拼接的字符串

现在有一个需求：将emp表相同部门的员工以逗号分隔合并到一起！！！
可能很多人已使用过wmsys.wm_concat函数，但wmsys.wm_concat是一个非公开函数，具有不确定性（返回值原来是varchar,Oracle11.2下就成了clob)。从Oracle11.2开始就有了分析函数listagg来替代它，到了Oracle 12C+及以后，wmsys.wm_concat函数已经被遗弃了，所以建议各位不要再使用这个函数了！至于listagg函数对于拼接超过4000长度的字符会报错问题，可以看博主这篇博客：
通过wm_concat 函数报错：ora06502-character string buffer to small浅谈wm_concat、Listagg、xmlagg函数的使用和结果不确定性
扯远了，接下来用listagg函数来实现上面的需求

SQL> select deptno,listagg(ename,',') within group(order by empno) as yonghu
  2  from emp
  3  group by deptno;

DEPTNO YONGHU
------ --------------------------------------------------------------------------------
    10 CLARK,KING,MILLER
    20 SMITH,JONES,SCOTT,ADAMS,FORD
    30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
       test

四、如何使用substr或regexp_substr提取第N个分隔符的子串

有如下临时表，每个部门的员工以逗号分隔：

SQL> select deptno,listagg(ename,',') within group(order by empno) as yonghu
  2  from emp where deptno is not null
  3  group by deptno;

DEPTNO YONGHU
------ --------------------------------------------------------------------------------
    10 CLARK,KING,MILLER
    20 SMITH,JONES,SCOTT,ADAMS,FORD
    30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

现在有一个需求：将上面临时表第二个子串取出来当作一列

没有正则表达式之前需要找到逗号的对应位置，然后对字符串进行截取：

with t as
 (select deptno, listagg(ename, ',') within group(order by empno) as yonghu
    from emp
   where deptno is not null
   group by deptno),
t1 as
 (select deptno,
         yonghu,
         instr(yonghu, ',', 1, 1) as 第一个逗号位置,
         instr(yonghu, ',', 1, 2) as 第二个逗号位置
    from t)    
select deptno,
       yonghu,
       case
         when 第二个逗号位置 = 0 then
          substr(yonghu, 第二个逗号位置 + 1)
         else
          substr(yonghu,
                 第一个逗号位置 + 1,
                 第二个逗号位置 - 第一个逗号位置 - 1)
       end as newstr
  from t1;
  
DEPTNO YONGHU                                                                           NEWSTR
------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
    10 CLARK,KING,MILLER                                                                KING
    20 SMITH,JONES,SCOTT,ADAMS,FORD                                                     JONES
    30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES                                             WARD

而使用正则表达式就很简单了！！！看下面写法

with t as (
select deptno,listagg(ename,',') within group(order by empno) as yonghu
from emp where deptno is not null
group by deptno)
select  deptno,yonghu,regexp_substr(yonghu,'[^,]+',1,2) as sub from t;

DEPTNO YONGHU                                                                           SUB
------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
    10 CLARK,KING,MILLER                                                                KING
    20 SMITH,JONES,SCOTT,ADAMS,FORD                                                     JONES
    30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES                                             WARD

怎么样，有些时候用正则实现起来一些复杂的需求是不是特简单！！！这就是为啥博主钟爱正则的原因！

五、分解IP地址

现在有一个ip

with t as
 (select '192.168.1.102' as ip from dual)

现在有个需求，要将这个ip以’.'拆分开来，那么使用传统的substr比较麻烦，但是正则的话就很简单，给出下面结果：

select regexp_count(ip, '\.'), regexp_substr(ip, '[^.]+', 1, level)
  from t
connect by level <= regexp_count(ip, '\.');
REGEXP_COUNT(IP,'\.') REGEXP_SUBSTR(IP,'[^.]+',1,LEVEL)
--------------------- ----------------------------------------------------------------
                    3 192
                    3 168
                    3 1

当然，如果一个表里有多条数据，你这么写会发现问题，比如下面这个sql

with t as
 (
 select '192.168.1.102' as ip from dual
 union all
  select '10.1.1.103' as ip from dual
 )
select regexp_count(ip, '\.'), regexp_substr(ip, '[^.]+', 1, level)
  from t
connect by level <= regexp_count(ip, '\.');
REGEXP_COUNT(IP,'\.') REGEXP_SUBSTR(IP,'[^.]+',1,LEVEL)
--------------------- ---------------------------------
                    3 192
                    3 168
                    3 1
                    3 1
                    3 1
                    3 1
                    3 1
                    3 10
                    3 168
                    3 1
                    3 1
                    3 1
                    3 1
                    3 1

14 rows selected

有没有发现你看不懂了？？？这个问题后面文章会解释，给大家介绍如何应对多行情况，现在先给大家留个念想~😁😁😁

总结

本篇文章相对上一篇数据处理文章有一定难度，但也是工作中常用的高级处理方式，我也算复习了一遍！

有关【SQL开发实战技巧】系列（十一）：拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数的更多相关文章

python - 如何使用 Ruby 或 Python 创建一系列高音调和低音调的蜂鸣声？ - 2
关闭。这个问题是opinion-based.它目前不接受答案。想要改进这个问题？更新问题，以便editingthispost可以用事实和引用来回答它.关闭4年前。Improvethisquestion我想在固定时间创建一系列低音和高音调的哔哔声。例如:在150毫秒时发出高音调的蜂鸣声在151毫秒时发出低音调的蜂鸣声200毫秒时发出低音调的蜂鸣声250毫秒的高音调蜂鸣声有没有办法在Ruby或Python中做到这一点？我真的不在乎输出编码是什么(.wav、.mp3、.ogg等等)，但我确实想创建一个输出文件。
ruby-on-rails - Rails 常用字符串(用于通知和错误信息等) - 2
大约一年前，我决定确保每个包含非唯一文本的Flash通知都将从模块中的方法中获取文本。我这样做的最初原因是为了避免一遍又一遍地输入相同的字符串。如果我想更改措辞，我可以在一个地方轻松完成，而且一遍又一遍地重复同一件事而出现拼写错误的可能性也会降低。我最终得到的是这样的:moduleMessagesdefformat_error_messages(errors)errors.map{|attribute,message|"Error:#{attribute.to_s.titleize}#{message}."}enddeferror_message_could_not_find(obje
ruby-on-rails - 使用一系列等级计算字母等级 - 2
这里是Ruby新手。完成一些练习后碰壁了。练习:计算一系列成绩的字母等级创建一个方法get_grade来接受测试分数数组。数组中的每个分数应介于0和100之间，其中100是最大分数。计算平均分并将字母等级作为字符串返回，即“A”、“B”、“C”、“D”、“E”或“F”。我一直返回错误:avg.rb:1:syntaxerror,unexpectedtLBRACK,expecting')'defget_grade([100,90,80])^avg.rb:1:syntaxerror,unexpected')',expecting$end这是我目前所拥有的。我想坚持使用下面的方法或.join，
ruby - 在没有 sass 引擎的情况下使用 sass 颜色函数 - 2
我想在一个没有Sass引擎的类中使用Sass颜色函数。我已经在项目中使用了sassgem，所以我认为搭载会像以下一样简单:classRectangleincludeSass::Script::FunctionsdefcolorSass::Script::Color.new([0x82,0x39,0x06])enddefrender#hamlengineexecutedwithcontextofself#sothatwithintemlateicouldcall#%stop{offset:'0%',stop:{color:lighten(color)}}endend更新:参见上面的#re
ruby-on-rails - 在 ruby 中使用 gsub 函数替换单词 - 2
我正在尝试用ruby中的gsub函数替换字符串中的某些单词，但有时效果很好，在某些情况下会出现此错误？这种格式有什么问题吗NoMethodError(undefinedmethod`gsub!'fornil:NilClass):模型.rbclassTest"replacethisID1",WAY=>"replacethisID2andID3",DELTA=>"replacethisID4"}end另一个模型.rbclassCheck 最佳答案啊，我找到了!gsub!是一个非常奇怪的方法。首先，它替换了字符串，所以它实际上修改了
ruby - 在 Ruby 中有条件地定义函数 - 2
我有一些代码在几个不同的位置之一运行:作为具有调试输出的命令行工具，作为不接受任何输出的更大程序的一部分，以及在Rails环境中。有时我需要根据代码的位置对代码进行细微的更改，我意识到以下样式似乎可行:print"Testingnestedfunctionsdefined\n"CLI=trueifCLIdeftest_printprint"CommandLineVersion\n"endelsedeftest_printprint"ReleaseVersion\n"endendtest_print()这导致:TestingnestedfunctionsdefinedCommandLin
ruby - 在 Ruby 中按名称传递函数 - 2
如何在Ruby中按名称传递函数？(我使用Ruby才几个小时，所以我还在想办法。)nums=[1,2,3,4]#Thisworks,butismoreverbosethanI'dlikenums.eachdo|i|putsiend#InJS,Icouldjustdosomethinglike:#nums.forEach(console.log)#InF#,itwouldbesomethinglike:#List.iternums(printf"%A")#InRuby,IwishIcoulddosomethinglike:nums.eachputs在Ruby中能不能做到类似的简洁？我可以只
【鸿蒙应用开发系列】- 获取系统设备信息以及版本API兼容调用方式 - 2
在应用开发中，有时候我们需要获取系统的设备信息，用于数据上报和行为分析。那在鸿蒙系统中，我们应该怎么去获取设备的系统信息呢，比如说获取手机的系统版本号、手机的制造商、手机型号等数据。1、获取方式这里分为两种情况，一种是设备信息的获取，一种是系统信息的获取。1.1、获取设备信息获取设备信息，鸿蒙的SDK包为我们提供了DeviceInfo类，通过该类的一些静态方法，可以获取设备信息，DeviceInfo类的包路径为：ohos.system.DeviceInfo.具体的方法如下：ModifierandTypeMethodDescriptionstatic StringgetAbiList()Obt
「Python｜Selenium｜场景案例」如何定位iframe中的元素？ - 2
本文主要介绍在使用Selenium进行自动化测试或者任务时，对于使用了iframe的页面，如何定位iframe中的元素文章目录场景描述解决方案具体代码场景描述当我们在使用Selenium进行自动化测试的时候，可能会遇到一些界面或者窗体是使用HTML的iframe标签进行承载的。对于iframe中的标签，如果直接查找是无法找到的，会抛出没有找到元素的异常。比如近在咫尺的例子就是，CSDN的登录窗体就是使用的iframe，大家可以尝试通过F12开发者模式查看到的tag_name,class_name,id或者xpath来定位中的页面元素，会抛出NoSuchElementException异常。解决
C51单片机——实现用独立按键控制LED亮灭（调用函数篇） - 2
说在前面这部分我本来是合为一篇来写的，因为目的是一样的，都是通过独立按键来控制LED闪灭本质上是起到开关的作用，即调用函数和中断函数。但是写一篇太累了，我还是决定分为两篇写，这篇是调用函数篇。在本篇中你主要看到这些东西！！！1.调用函数的方法（主要讲语法和格式）2.独立按键如何控制LED亮灭3.程序中的一些细节（软件消抖等）1.调用函数的方法思路还是比较清晰地，就是通过按下按键来控制LED闪灭，即每按下一次，LED取反一次。重要的是，把按键与LED联系在一起。我打算用K1来作为开关，看了一下开发板原理图，K1连接的是单片机的P31口，当按下K1时，P31是与GND相连的，也就是说，当我按下去时