Oracle 函数大全

https://www.cnblogs.com/bbliutao/archive/2017/11/08/7804263.html

F.1字符函数——返回字符值

(chr,concat,initcap,lower,lpad/rpad,nls_initcap,nls_lower,nls_upper,regexp_replace,regexp_substr,replace,trim/ltrim/rtrim,soundex,substr,translate,upper)

说明：可以sql和plsql中使用

CHR

语法: chr(x)

功能：给出整数X，返回对应的ASCII码字符。CHR和ASCII是一对反函数。

SQL> select chr(54740) 别名1,chr(65) 别名2 from dual;

别名1 别名2

赵 A

---

CONCAT

语法： CONCAT（string1,string2）

功能：连接两个字符串

SQL> select concat('010-','88888888')||'23' 连接 from dual;

连接

010-8888888823

---

INITCAP

语法：INITCAP（string）

功能：返回字符单词首字母大写，其余小写，单词用空格和非字母字符分隔。

SQL> select initcap('smith hEllo') upp from dual;

UPP

Smith Hello

---

LOWER

语法：LOWER(string)

 功能：所以字母小写

 SQL> select lower('AaBbCcDd') AaBbCcDd from dual;



 AaBbCcDd

 aabbccdd

---

LPAD/RPAD

语法：LPAD/RPAD（string1,x[,string2]）

功能：在string1字符左边或右边粘贴数个string2字符，直到字符总字节数达到x字节。string2默认为空格。

如果string2的长度要比X字符少，就按照需要进行复制。如果string2多于X字符，则仅string2前面的X各字符被使用。如果string1长度大于x,则返回string1左端x个字符。

RPAD 在列的右边粘贴字符

LPAD 在列的左边粘贴字符

SQL> select lpad(rpad('gao',10,''),17,'')from dual;

LPAD(RPAD('GAO',1

*******gao*******

不够字符则用*来填满

---

NLS_INITCAP

语法：NLS_INITCAP（string[,nlsparams]）

功能：返回字符串每个单词第一个字母大写而单词中的其他字母小写的string，nlsparams

指定了不同于该会话缺省值的不同排序序列。如果不指定参数，则功能和INITCAP相同。Nlsparams可以使用的形式是：‘NLS_SORT=sort’ 这里sort制订了一个语言排序序列。

---

NLS_LOWER

语法：NLS_LOWER（string[,nlsparams]）

功能：返回字符串中的所有字母都是小写形式的string。不是字母的字符不变。

Nlsparams参数的形式与用途和NLS_INITCAP中的nlsparams参数是相同的。如果nlsparams没有被包含，那么NLS_LOWER所作的处理和LOWER相同。

---

NLS_UPPER

语法：NLS_UPPER（string[,nlsparams]）

功能：返回字符串中的所有字母都是大写的形式的string。不是字母的字符不变。nlsparams参数的形式与用途和NLS_INITCAP中的相同。如果没有设定参数，则NLS_UPPER功能和UPPER相同。

使用位置：过程性语句和SQL语句。

---

REGEXP_REPLACE

语法：REGEXP_REPLACE(str1,pattem[,str2[,pos[,occ[,par]]]])

功能：10g新增函数，扩展了REPLACE函数的功能，并且用于按照特定正则表达式的规则替换字符串。其中参数str1指定源字符表达式，pattem指定正则表达式，str2指定替换字符串，pos指定起始搜索位置，occ指定替换出现的第几个字符串，par指定默认匹配操作的文本串。

select REGEXP_REPLACE(a,’(.)’,’\1’) a from count;

A r g e n t i n a

体会NVL为DECODE，只支持NVL()内不再有其它括号()

select a,

   instr(upper(a), 'NVL(', 1) a3,

   instr(upper(a), ')',instr(upper(a), 'NVL(', 1),1) a4,

   substr(a,instr(upper(a), 'NVL(', 1),instr(upper(a), ')',instr(upper(a), 'NVL(', 1),1)-instr(upper(a), 'NVL(', 1)+1) a41,

   substr(a,instr(upper(a), 'NVL(', 1)+4,instr(upper(a), ')',instr(upper(a), 'NVL(', 1), 1)-instr(upper(a), 'NVL(', 1)-4) a5,

   REGEXP_REPLACE(

   substr(a,instr(upper(a), 'NVL(', 1)+4,instr(upper(a), ')',instr(upper(a), 'NVL(', 1), 1)-instr(upper(a), 'NVL(', 1)-4),

   '(.*),(.*)','\2,\1'

   ) a6,

   REGEXP_REPLACE(

   substr(a,instr(upper(a), 'NVL(', 1)+4,instr(upper(a), ')',instr(upper(a), 'NVL(', 1), 1)-instr(upper(a), 'NVL(', 1)-4),

   '(.*),(.*)','decode(\1,null,\2,'''',\2,\1)'

   ) a7,

   substr(a,1,instr(upper(a), 'NVL(', 1)-1)||REGEXP_REPLACE(

   substr(a,instr(upper(a), 'NVL(', 1)+4,instr(upper(a), ')',instr(upper(a), 'NVL(', 1), 1)-instr(upper(a), 'NVL(', 1)-4),

   '(.*),(.*)','decode(\1,null,\2,'''',\2,\1)'

   )||substr(a,instr(upper(a), ')',instr(upper(a), 'NVL(', 1), 1)+1) a8

from temp_liut a;

---

判断是否是数字

regexp_replace(a, '\d+', '') is null

REGEXP_SUBSTR

语法：REGEXP_SUBSTR(str1,pattem [,pos[,occ[,par]]])

功能：10g新增函数，扩展了SUBSTR函数的功能，并且用于按照特定表达式的规则返回字符串的子串。其中参数str1指定源字符表达式，pattem指定规则表达式， pos指定起始搜索位置，occ指定替换出现的第几个字符串，par指定默认匹配操作的文本串。

Select REGEXP_SUBSTR(‘http://www.oracle.com/products’,’http://([[:alnum:]]+.?)’) a from dual;

a

http://www.oracle.com/

---

REPLACE

语法：REPLACE（string，search_str[,replace_str]）

功能：把string中的所有的子字符串search_str用可选的replace_str替换，如果没有指定replace_str，所有的string中的子字符串search_str都将被删除。REPLACE是TRANSLATE所提供的功能的一个子集。

REPLACE('string','s1','s2')

string 希望被替换的字符或变量

s1 被替换的字符串

s2 要替换的字符串

SQL> select replace('he lohe you','he','i') from dual;

replace('he lohe you','he','i')

i loi you

---

TRIM/LTRIM/RTRIM

语法1：LTRIM/RTRIM（string1,[string2]）

语法2：trim([string2] from string1)

语法1功能：中删除从左/右边算起出现在string1中的字符string2，string2如果是多个字符则逐个单字符比对删除,tring2被缺省设置为单个的空格。当遇到不在string2中的第一个字符，结果就被返回了；

语法2功能：删除左右两边出现在string1中的字符string2，tring2必须为单字符，否则报错。

select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;

gao qian jing

select ltrim('abaaaabbbcda','ab') from dual;

cda

select trim('a' from 'abacda') from dual;

bacd

---

SOUNDEX

语法: SOUNDEX（string）

功能: 返回string的声音表示形式.这对于比较两个拼写不同但是发音类似的单词而言很有帮助，如果字符发音相同，则返回的结果会一致.

SOUNDEX 返回一个与给定的字符串读音相同的字符串

SQL> create table table1(xm varchar(8));

SQL> insert into table1 values('weather');

SQL> insert into table1 values('wether');

SQL> insert into table1 values('gao');

SQL> select xm from table1 where soundex(xm)=soundex('weather');

XM

weather

wether

---

SUBSTR

语法: SUBSTR（string,a[,b]）

功能：截取字符串，从第a个开始取b个字符，这个务必要注意，是字符。 vachar2最长4000个字节，GBK编码中一个中文字符占2个字节，韩文字符占4个字节，如果string是date或者number的数据类型，会自动转化为varchar2。

SQL> select substr('13088888888',3,8) 截取字符串 from dual;

截取字符串

08888888

select SUBSTR(t.a,4),a from temp_liut t;

JAN-00 04-jan-00

---

TRANSLATE

语法: TRANSLATE(string,from_str,to_str)

功能: 将字符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.

Select TRANSLATE('2abc2234','01234abcde','99999XXXXX') tra from dual

9XXX9999

select replace(TRANSLATE('as中国fd1234','1234567890','0000000000'),'0') from dual;

查找字符串',01234,2342,2,'中逗号出现次数

select length(translate(',01234,2342,2,', 'a0123456789', ' ')) from dual;

判断字符串是否是数字

replace(translate(a, '0123456789', '0'),'0') is null

regexp_replace(a, '\d+', '') is null

UPPER

语法: UPPER（string）

功能: 所有字母大写.（不是字母的字符不变.如果string是CHAR数据类型的,那么结果也是CHAR类型的.如果string是VARCHAR2类型的,那么结果也是VARCHAR2类型的）.

SQL> select upper('AaBbCcDd') upper from dual;

UPPER

AABBCCDD

---