oracle中无规律数字和汉字组合取数字|函数小结|

浙江木木提的需求，摘要后面的几位是有规律的，使用substr搞定，可是前面的数量因为有一百一千一个等不能确定，通过下面的几个函数搞定了。
首先看sql：

select gl_detail.explanation, substr(gl_detail.explanation,-6,6),
replace(gl_detail.explanation,substr(gl_detail.explanation,-6,6),'哈'),
translate(replace(gl_detail.explanation,substr(gl_detail.explanation,-6,6),'哈'),'9876543210'||replace(gl_detail.explanation,substr(gl_detail.explanation,-6,6),'哈'), '9876543210'),
       bd_accsubj.dispname,
       gl_detail.debitamount 借方,
       gl_detail.creditamount 贷方,
       gl_detail.prepareddatev 制单日期,
       gl_voucher.no 凭证号,
       wmsys.wm_concat(gl_freevalue.valuecode) valuecode,
       wmsys.wm_concat(gl_freevalue.valuename) valuename,
       gl_detail.detailindex,
       gl_detail.pk_systemv
  from bd_accsubj
  join gl_detail
    on gl_detail.pk_accsubj = bd_accsubj.pk_accsubj
  join bd_glorgbook
    on bd_glorgbook.pk_glorgbook = bd_accsubj.pk_glorgbook
  join gl_voucher
    on gl_detail.pk_voucher = gl_voucher.pk_voucher
  left join gl_freevalue
    on gl_detail.assid = gl_freevalue.freevalueid
 where gl_detail.dr = '0'
   and gl_detail.explanation<>'期初'
   and gl_detail.yearv = '2012'
   and gl_detail.periodv='05'
   and bd_glorgbook.glorgbookcode = '010101-0001'
--and bd_accsubj.subjcode like '150103%'
 group by gl_detail.explanation,
          bd_accsubj.dispname,
          gl_detail.creditamount,
          gl_detail.debitamount,
          gl_detail.prepareddatev,
          gl_voucher.no,
          gl_detail.detailindex,
           gl_detail.pk_systemv
 order by gl_detail.prepareddatev, gl_voucher.no

首先学习各种函数
1，http://www.cnblogs.com/liehuzuo/archive/2011/09/21/2184273.html 猎-户-座 oracle translate() 详解+实例

一、语法：
TRANSLATE(string,from_str,to_str)
二、目的
返回将（所有出现的）from_str中的每个字符替换为to_str中的相应字符以后的string。TRANSLATE 是 REPLACE 所提供的功能的一个超集。如果 from_str 比 to_str 长，那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除，因为它们没有相应的替换字符。to_str 不能为空。Oracle 将空字符串解释为 NULL，并且如果TRANSLATE 中的任何参数为NULL，那么结果也是 NULL。

三、允许使用的位置
过程性语句和SQL语句。
四、示例
Sql代码

1. SELECT TRANSLATE('abcdefghij','abcdef','123456') FROM dual;
2. TRANSLATE (
3. --------------
4. 123456ghij
5.
6. SELECT TRANSLATE('abcdefghij','abcdefghij','123456') FROM dual;
7. TRANSL
8. ----------
9. 123456

语法：TRANSLATE(expr,from,to)

expr: 代表一串字符，from 与 to 是从左到右一一对应的关系，如果不能对应，则视为空值。

举例：

select translate('abcbbaadef','ba','#@') from dual　（b将被＃替代，a将被＠替代）

select translate('abcbbaadef','bad','#@') from dual　（b将被＃替代，a将被＠替代，d对应的值是空值，将被移走）

因此：结果依次为：@#c##@@def 和@#c##@@ef
语法：TRANSLATE(expr,from,to)

expr: 代表一串字符，from 与 to 是从左到右一一对应的关系，如果不能对应，则视为空值。

举例：

select translate('abcbbaadef','ba','#@') from dual　（b将被＃替代，a将被＠替代）

select translate('abcbbaadef','bad','#@') from dual　（b将被＃替代，a将被＠替代，d对应的值是空值，将被移走）

因此：结果依次为：@#c##@@def 和@#c##@@ef

示例如下：

示例一：将数字转换为9，其他的大写字母转换为X，然后返回。

SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"
FROM DUAL

示例二：将数字保留，将其他的大写字母移除。

SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'0123456789') "Translate example"
FROM DUAL

罗勇补充示例如下：

示例三：示例证明是按照字符来处理，不是按照字节来处理，如果to_string的字符数比from_string多的话，多出的字符数似乎没有什么用处，也不会引发异常。

SELECT TRANSLATE('我是中国人,我爱中国', '中国', 'China') "Translate example"

FROM DUAL

示例四：下面的示例证明，如果from_string的字符数大于to_string，那么多出的字符会被移除，也就是ina三个字符会从char参数中移除，当然区分大小写啦。

SELECT TRANSLATE('I am Chinese, I love China', 'China', '中国') "Translate example"

FROM DUAL

示例五：以下示例证明，如果第二个参数为空字符串，整个返回null。

SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'') "License"
FROM DUAL

示例六：在银行转帐时经常看见账户人只显示姓名的最后一个字，其余的用星号代替，我就用translate来做个类似的东西吧。

SELECT TRANSLATE('中国人',

substr('中国人',1,length('中国人') - 1),

rpad('*',length('中国人'),'*')) "License"

FROM DUAL

2，http://hi.baidu.com/xbnh0217/blog/item/22598da0ef8bf18746106465.html
oracle中translate 很好用呀字段中的所有汉字剔除

实例

select lmark3, translate( t.lmark3, '9876543210' ||t.lmark3, '9876543210')from line_data_all_t t

语法：TRANSLATE(char, from, to)

用法：返回将出现在from中的每个字符替换为to中的相应字符以后的字符串。
            若from比to字符串长，那么在from中比to中多出的字符将会被删除。
            三个参数中有一个是空，返回值也将是空值。

举例：SQL> select translate('abcdefga','abc','wo') 返回值 from dual;
            返回值
            -------
            wodefgw

分析：该语句要将'abcdefga'中的'abc'转换为'wo',
            由于'abc'中'a'对应'wo'中的'w',
            故将'abcdefga'中的'a'全部转换成'w';
            而'abc'中'b'对应'wo'中的'o',
            故将'abcdefga'中的'b'全部转换成'o';
            'abc'中的'c'在'wo'中没有与之对应的字符,
            故将'abcdefga'中的'c'全部删除;

            简单说来，就是将from中的字符转换为to中与之位置对应的字符，
            若to中找不到与之对应的字符，返回值中的该字符将会被删除。

            在实际的业务中，可以用来删除一些异常数据，
            比如表a中的一个字段t_no表示电话号码，
            而电话号码本身应该是一个由数字组成的字符串，
            为了删除那些含有非数字的异常数据，
            就用到了translate函数：
            SQL> delete from a，
                      where length(translate(trim(a.t_no),
                                            '0123456789' || a.t_no,
                                            '0123456789')) <> length(trim(a.t_no));

2.replace

语法：REPLACE(char, search_string,replacement_string)

用法：将char中的字符串search_string全部转换为字符串replacement_string。

举例：SQL> select REPLACE('fgsgswsgs', 'fk' ,'j') 返回值 from dual;
            返回值
            ---------
            fgsgswsgs

            SQL> select REPLACE('fgsgswsgs', 'sg' ,'eeerrrttt') 返回值 from dual;
            返回值
            -----------------------
            fgeeerrrtttsweeerrrttts

分析：第一个例子中由于'fgsgswsgs'中没有与'fk'匹配的字符串，
            故返回值仍然是'fgsgswsgs'；
            第二个例子中将'fgsgswsgs'中的字符串'sg'全部转换为'eeerrrttt'。

总结：综上所述，replace与translate都是替代函数，

只不过replace针对的是字符串，而translate针对的是单个字符。
可以继续学习http://zhidao.baidu.com/question/214512520.html
更新：oracle中如果知道了后面的位数可以方便使用substr搞定，可是如何得到字段中去除这些有规律之后的字段值呢？
可以使用replace(gl_detail.explanation,substr(gl_detail.explanation,-6,6),''),
这里的''巧妙的将substr取到的东西使用空值‘’替代
更新decode和sigh函数
原文地址：http://wentao365.iteye.com/blog/11568891：比较大小函数 SIGN

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1 ,例如：
引用a=10，b=20
则sign(a-b)返回-1
2：流程控制函数 DECODE

DECODE函数是ORACLE PL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商还没有实现此功能。假设想给职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，8000 元的不加。实现：
select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary*1.15) from employee

是不是很简洁？

DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。初看一下，DECODE 只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使DECODE函数具备大于、小于或等于功能。

含义解释:
引用decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

3，Oracle lower(Upper)函数|大小写|

ower

select lower(user_name) from user

将 user表里的user_name字段信息中含有字母的全部转成大写的方法：

update user　set 　user_name=Upper(user_name)

同理得到将 user表里的user_name字段信息中含有字母的全部转成小写的方法：

update user　set 　user_name=lower(user_name)

4，substr

　　Oracle中的截取字符串函数。　

　语法如下： substr( string, start_position, [ length ] ) 　　

参数分析： string 字符串值，　start_position 截取字符串的初始位置， Number型，start_position为负数时，表示从字符串右边数起。 length 　截取位数，Number型　

其中，length为可选，如果length为空（即不填）则返回start_position后面的所有字符。　　

意思就是：从start_position开始,取出length个字符并返回取出的字符串。

　示例： SELECT substr('This is a test', 6, 2) FROM dual 返回 'is' 　　substr('SyranMo have a dream', -8, 2)

posted on 2012-05-26 17:26 sumsen 阅读(5061) 评论(0) 编辑收藏举报

刷新页面返回顶部

SUMSEN