oracle函数

 

1）lpad(s, len, inserted)在字符串的左边插入字符,已达到指定长度，

     rpad(s, len, inserted)在字符串右边插入字符,已达到指定长度

     select lpad('123', 2, 'A') from dual; --> 12
     select lpad('123', 4, 'AB') from dual; --> A123
     select lpad('123', 5, 'ABC') from dual; --> AB123

 

 

2）substr(s, startIndex, length)求字串, 当startIndex取非负数数时（0和1意思一致，表示从左边第一个字符开始）,表示从左边

    开始第startIndex个字符取字串。当startIndex取负数时，表示从右边第|startIndex|字符开始;

    length表示截取的字串才度（可不填），当length长度很大或不填时 会返回s从startIndex开始的所有字符。

 

3）替换函数，有两个经典的函数：replace，ranslate；两个函数的方法签名都一样。

replace:字符串级别替换，eg：select replace('acdd', 'cd','ef') from dual; --> aefd

translate: 字符级别替换，eg：select translate('acdd', 'cd', 'ef') from dual; --> aeff

分别详解：

 

replace: 语法 replace(char, search_string, [,replacement])

replace returns char with every occurence of search_string replaced with replacement_string. if replacement_string is ommited or null, then all occuurences of search_string are removed. If search_string is null, then char is returned.

 

解释：replace中，每个search_string被replacement_string所替换.

select replace('acdd','cd','ef') from dual; --> aefd

 

如果replacement_string是空或为NULL，那么char中的所有search_string被移除。

select replace('acdd', 'cd', '') from dual; --> ad

select replace('acdd', 'cd') from dual; --> ad

 

如果search_string为null，那么返回原来的char.

select replace('acdd','','ef') from dual; --> acdd

select replace('acdd','','ef') from dual; --> acdd

 

Both search_string are replacement_string, as well as char, can be any of the datatypes CHAR,VARCHAR2,NCHAR,

NVARCHAR2, CLOB, or NCLOB. The string returned is of varchar2 datatype and is in the same character set as char.

 

REPLACE function provides functionality related to that provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-on substitution. REPLACE lets you substitute one string for another as well as to remove character strings

 

translate:

 

语法： translate(char, from_string, to_string)

TRANSLATE returns char with all occurences of each character in from_string replaced by its corresponding character in to_string. Characters in char that are not in from_string are not replaced.  The argument from_string can contains more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If there extra character appear in char, then they are removed from the return value. You can not use an empty string for to_string to remove all characters in from_string from the return value. Oracle interprets the empty string as null, and if this function has a null argument, then it returns null.

 

解释：translate中，每个from_string中的字符被to_string中相应的字符所替换。

select translate('acdd', 'cd', '') from dual; --> null

select translate('acdd', '', '') from dual; --> null

 

实战：如何判断一个字符串是否是数字

解： 先转化：由于to_string不能为空，我们巧用#号代替

select translate('abc123', '#0123456789.', '#') from dual; --> abc

 

from_string中#被to_string中#代替,但char中没有#，所以保证了to_string不为空的规则。接下来，from_string中的数字和小数点都被替换成了空。原字符只留下abc三个字符。

转换好以后，用nvl2判断即可

select nvl2(translate('abc1243', '#0123456789.', '#'), '字符','数字') from dual;

 

4）非空判断函数nvl, nvl2

nvl(checked_value, replaced_value)

判断checked_value是否为空，如果为空用replaced_value返回，否则用checked_value返回

 

nvl2(checked_value, replaced_value1, replaced_value2)

判断checked_value是否为NULL，如果是返回replaced_value1，否则返回replaced_value2

 

 

5）求模函数 mod(5, 3)  结果为2

 

6）char(n)字段用max函数取出放入varchar2变量出错解决办法，场景如下：

create table tmp_table(
  id char(32)
);--表

 

insert into tmp_table values('123456789012345678901234567890AA');--记录

 

declare
  tmp_str varchar2(128);
begin
  --下面语句出错
  select max(id) into tmp_str from tmp_table;
  dbms_output.put_line(tmp_str);
end;

 

修改方法：

  select max(to_char(id)) into tmp_str from tmp_table;

想想这个char(n)类型真够操蛋的，还要to_char转换。还不如直接用varchar2(n)了, hehe~~