Oracle通过substr,instr,translate函数的组合获取日期分割表

前言

写这一篇文章也是因为项目从SQL数据库导入Oracle数据库中遇到的问题，主要是我们要导入的数据有年月的分割表的查询。

实现要求

我们要获取到tJkSale开头的所有带年月的分割表，如tJkSale201705等。我们还是要的Odbc连接的那个SQL数据库。

  select "name" from sysobjects@odbc_sql where "name" like 'tJkSale______';

通过上面的语句我们查询到了所有tJkSale开头的表

但是上面红框里的几个表并不是我们想要的表名，所以我们要想个方法把这些不显示出来。

实现思路

因为我们想要的表名最后四位就是YYYYMM代表年月的数字，想要获取对应的表名我们可以分为三步：

1. 获取表名最后六位。（substr）

2. 然后通过转义的方式把是数字的转换为一个特殊字符。(translate)

3. 判断字符串里是否存在这个特殊字符。(instr)

代码实现

通过substr函数获取表名最后六位

substr(string, a, b);

参数：

string 需要截取的字符串
a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
b 要截取的字符串的长度，最后一个参数也可以不用，这样就代表从第a个字符开始截取后面所有的字符串。

  select substr("name",length("name")-5,6) from sysobjects@odbc_sql where "name" like 'tJkSale______';

上图中可以看到我们用substr的函数只显示最后6位的名称了。

---

通过translate函数将数字转换为特殊字符“/”

translate(string, from, to)

参数：

string 需要转换的数据源

from  需要替换的字符

to 替换后的字符

  select translate(substr("name",length("name")-5,6),'1234567890','//') 
  from sysobjects@odbc_sql where "name" like 'tJkSale______';

可以看到通过translate后把数字都替换为/了，这样的话我们可以直接判断不是六个//就是我们想要的数据了

注：translate的第二个参数和第三个参数中的长度要对应，一开始我还没了解这个函数时写法时用的第三个参数只有一个‘/’，导致查询的结果会有不同个数的/出现，如下图：

---

判断字符串里是否存在这个特殊字符

instr( string1, string2 [, start_position [, nth_appearance ] ] )

参数：

string1 源字符串

string2 目标字符串

start_position 起始位置

nth_appearance 匹配序号

后两个参数可以不要的

string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。

  select instr(translate((substr("name",length("name")-5,6)),'1234567890','//'),'/') 
  from sysobjects@odbc_sql where "name" like 'tJkSale______';

通过上图我们可以看到，用instr查找包含'/'的值最后都是返回1，如果不包含‘/’返回是0。

---

最终我们要查询的方法

  select "name" from sysobjects@odbc_sql where "name" like 'tJkSale______'
  and instr(translate((substr("name",length("name")-5,6)),'1234567890','/'),'/')=1;

或是第二个方法

 select "name" from sysobjects@odbc_sql where "name" like 'tJkSale______'
  and translate((substr("name",length("name")-5,6)),'1234567890','//')='//';

相对来说推荐第二种方法，因为上面的方法如果后6位中有一个数字的话也会算在内了，并不是我们想要的结果，查询最终结果如下图：

-END-

Vaccae的往期经典

---

OpenCV

《C++ OpenCV案例实战---卡号获取》

《C++ OpenCV案例实战---卡片截取（附代码）》

《C++ OpenCV透视变换---切换手机正面图片》

《C++ OpenCV实战---获取数量》

《C++ OpenCV实战---利用颜色分割获取数量》

---

Android

《Android利用SurfaceView结合科大讯飞修改语音实别UI》

《Android关于语音识别的功能实现分析（一）---结构化思维》

《Android关于语音识别的功能实现分析（二）---语义解析》

《Android根据类生成签名字符串》

《Android碎片化布局fragment的实战应用》

《Android中RecyclerView嵌套RecyclerView》

《Android里用AsyncTask后的接口回调》

---

.Net C#

《C#自定义特性(Attribute)讲解与实际应用》

《C#根据类生成签名字符串（附DEMO下载地址）》

《C++创建动态库C#调用》

《C#与三菱PLC(型号FX2N)串口通讯类》

---

数据库及其它

《Oracel存储过程写报表实战》

《Delphi轮播视频和图片程序（用于双屏显示程序）》

《SQL随机增加销售数据的脚本编写（附脚本下载地址）》

《Oracle通过ODBC连接SQL Server数据库》

---

长按下方二维码关注微卡智享