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-