几个常见函数的非常见用法

转载地址：http://blog.itpub.net/12932950/viewspace-662628/

1、instr
instr函数用于定位指定字符串某次出现的位置。
通常情况，都是根据顺向查找，即从左向右查找，比如有字符串：1,2,3,4,5,6
现在要查找第3个","出现的位置，则可以：
select instr('1,2,3,4,5,6',',',1,3) from dual;

但是如果要查找最后一个","出现的位置，而且个数又不确定，按照上面的思路就无法简单的实现了
于是就有了反向查找这个用法：
select instr('1,2,3,4,5,6',',',-1) from dual;
这个-1便指明了定位的方向是从右向左查找。

2、translate(src, rep_str, to_str)
这个函数用于替换src中存在rep_str的字符按顺序替换为to_str中的字符。
比如要将上个例子中的字符串中的替换为"a,b,c,d,e,f"，则可以：
select translate('1,2,3,4,5,6', '1234567890', 'abcdefghij') from dual

但是，如果要将字符串中的123456等数字去掉而保留","，该如何做呢。
于是translate函数的特殊用法出现了：
select translate('1,2,3,4,5,6', '^1234567890', '^') from dual 
可以在rep_str中指定一个在src中未出现的字符，然后替换目标to_str为该字符即可去掉这些数字。

还有一种用法也很是值得一提：
假如我有三组数字，每组数字都是由1-9中的三个数字组成，且这三组数字之间没有一个数字是重复的。比如：
123   456    789
就是没有重复的。但是：
123   345    567
就是有重复的。那如何实现这样的无重复的三组数字呢？
这里translate就有可以派上用场了：
select translate('123456789', '$' || n1 || n2 || n3, '$') from dual;
只要判断上述结果是否为空即可。
原理其实和前面一个例子相同，只不过这里用法上反过来了，思路实在是妙啊。

3、reverse(str)
将字符串的顺序反转。如：
select reverse('1,2,3,4,5,6') from dual;

4、dump
这个命令可以得到所输入的数据的类型、长度及16进制转换后的值。
当dump(str, 1010)时，可以得到这个字符串的字符集。

5、extract
截取时间的某个部分。
比如天：extract(day from current_timestamp)

6、to_dsinterval
interval时间段的函数转换，格式为：dd hh24:mi:ss
如：select sysdate + to_dsinterval('0 0:0:30') from dual
表示加30秒。

7、numtodsinterval/numtoyminterval
这里的ds和ym的意思分别是day to second和year to month。理解了这个含义，那么函数的功能和差别也就自然而然的明白了。

他们和第六个函数的作用相似。不过功能上更加丰富和灵活一点。
比如说，当前时间+1天：
sysdate + numtodsinterval(1, 'day')
当前时间+23秒：
sysdate + numtodsinterval(23,'second')
而且可以动态增长：
select sysdate + numtodsinterval(rownum, 'second') 
  from dual connect by rownum <= 10

这个用法比直接的Interval灵活多了，直接的interval不能实现动态的增加。
可以这么做：
select sysdate + interval '23' second 
  from dual connect by rownum <= 10
但是不能这么做：
SQL> select sysdate + interval to_char(rownum) second
  2    from dual connect by rownum <= 10
  3  /
 
select sysdate + interval to_char(rownum) second
  from dual connect by rownum <= 10
 
ORA-00923: 未找到要求的 FROM 关键字
无法实现动态递增。