Oracle数据库常用的函数

1. 字符函数

1.concat(str1,str2)字符串拼接函数
      示例
      select concat('a','b') as AB from dual 结果 ab
      select 'a'||'b' as AB from dual 结果ab                         concat('a','b')等价于 'a'||'b'
      在模糊查询中 还可以用concat(cloum1,cloum2) like %*%   表示   cloum1,cloum2两个字段来进行比较

2.initcap(str)将每个单词首字母大写，其他字母小写
      示例
      select initcap('aA bB') as AB  from dual; 结果 Aa Bb
      select initcap('aAbB') as AB  from dual; 结果 Aabb
      select initcap('a A b  B') as AB  from dual; 结果 A A B  B
      按照空格分隔单词 首字母大写
      
3.instr(x,find_string[,start][,occurrence])返回指定字符串在某字符串中的位置，可以指定开始位置和第几次出现结果（[]表示非必填）
      示例
      select instr('AAABBBB','B') as AB from dual;; 结果 4
      select instr('AAABBBB','B',1,4) as AB from dual;; 结果 7
      select instr('AAABBBB','b',5) as AB from dual;; 结果0
      起始位置和第几次出现不填写，默认从第一个开始第一次出现，严格区分大小写
      
4.length(str)返回表达式中的字符长度
      示例
      select length('AAAA BBBbbb') as AB from dual 结果 11
      select length('中 文') as AB from dual  结果 3

5.lower(str)将字符串转换为小写
      示例
      select lower('AAAA BBBbbb') as AB from dual 结果 aaaa bbbbbb

6.upper(str)将字符串转换为大写
      示例
      select upper('aaaa BBBbbb') as AB from dual 结果 AAAA BBBBBB

7.lpad(str,width[,pad_string])当字符串长度不够时，左填充补齐，可以指定补齐时用什么字符补齐,若不指定，则以空格补齐（[]表示非必填）
      示例
      select lpad('AAAAbbb',13) as AB from dual; 结果       AAAAbbb
      select lpad('AAAAbbb',13,'--') as AB from dual; 结果 ------AAAAbbb

8.rpad(str,width[,pad_string])当字符串长度不够时，右填充补齐，可以指定补齐时用什么字符补齐,若不指定，则以空格补齐（[]表示非必填）
      示例
      select rpad('AAAAbbb',13) as AB  from dual; 结果AAAAbbb      
      select rpad('AAAAbbb',13,'--') as AB from dual; 结果AAAAbbb------

9.ltrim(x[,trim_string])从字符串左侧去除指定的所有字符串，若没有指定去除的字符串，则默认去除左侧空白符（[]表示非必填）
      示例
      select ltrim('      AAAAbbb') as AB from dual; 结果AAAAbbb      
      select ltrim('A AAAbbb A','A') as AB from dual; 结果AAAbbb A

10.rtrim(x[,trim_string])从字符串右侧去除指定的所有字符串，若没有指定去除的字符串，则默认去除左侧空白符（[]表示非必填）
      示例
      select rtrim('AAAAbbb      ') as AB from dual; 结果AAAAbbb      
      select rtrim('A  AAAbbb  A','A') as AB from dual; 结果A  AAAbbb  
   
11.trim(trim_string from x)从字符串两侧去除指定的所有字符串
	   示例
	   select trim ( 'x' from 'xxxxDWEYExxxx') as AB from dual; 结果DWEYE （注意要指定的只能单个字符）

12.nvl(x,value)将一个NULL转换为另外一个值，如果x为NULL,则返回value,否则返回x值本身
	   示例
	   select nvl(null,'ab') as AB from dual; 结果ab
	   select nvl('df','ab') as AB from dual; 结果df

13.nvl2(x,value1,value2),如果x不为NULL，返回value1，否则，返回value2
	   示例
	   select nvl2(null,'ab','cd') as AB from dual; 结果cd
	   select nvl2('df','ab','cd') as AB from dual; 结果ab

14.replace(x,search_string,replace_string),从字符串x中搜索search_string字符串，并使用replace_string字符串替换,不会更改数据库中原始值
	   示例
	  select replace('acddsdsdsad','d','*') as AB from dual; 结果ac**s*s*sa*

15.substr(x,start[,length])返回字符串中的指定的字符，这些字符从字符串的第start个位置开始，长度为length个字符；如果start是负数，则从x字符串的末尾开始算起；如果length省略，则将返回一直到字符串末尾的所有字符（[]表示非必填）
	   示例
	  select substr('sdsdadsd',3) as AB from dual; 结果sdadsd（取起始值）
	  select substr('sdsdadsd',3,5) as AB from dual; 结果sdads（第三个开始包含第3个，取5个）

16.decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)根据条件得到自定义值
	   示例
	    select decode(3,1,3,2,4,5) as AB from dual; 结果5
	    select decode(1,1,3,2,4,5) as AB from dual; 结果3
	    select decode(2,1,3,2,4,5) as AB from dual; 结果4
	    可以配合其他函数一起使用，条件也可以是查询语句，返回值也能是函数如sysdat

以下部分原文地址
2. 数字函数

 1.  ROUND(X[,Y])，四舍五入。

		在缺省 y 时，默认 y=0；比如：ROUND(3.56)=4。
		
		y 是正整数，就是四舍五入到小数点后 y 位。select ROUND(5.654,2) as AB from dual 结果5.65。
		
		y 是负整数，四舍五入到小数点左边|y|位。select ROUND(351.654,-2) as AB from dual 结果400。

 2.  TRUNC(x[,y])，直接截取，不四舍五入。

		在缺省 y 时，默认 y=0；比如：TRUNC (3.56)=3。
		
		Y是正整数，就是四舍五入到小数点后 y 位。TRUNC (5.654,2)=5.65。
		
		y 是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。

3. 日期函数

   日期函数对日期进行运算。常用的日期函数有：

1、ADD_MONTHS(d,n)，在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期。

		d 表示日期，n 表示要加的月数。
		
		例：SELECT SYSDATE,add_months(SYSDATE,5) FROM dual;

 

2、LAST_DAY(d)，返回指定日期当月的最后一天。

		例：SELECT SYSDATE,last_day(SYSDATE) FROM dual;

 

3、ROUND(d[,fmt])，返回一个以 fmt 为格式的四舍五入日期值， d 是日期， fmt 是格式

   模型。默认 fmt 为 DDD，即月中的某一天。

		Ø  ① 如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日，即前半年舍去，后半年作为下一年。
		
		Ø  ② 如果 fmt 为“MONTH”则舍入到某月的 1 日，即前月舍去，后半月作为下一月。
		
		Ø  ③ 默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。
		
		Ø  ④ 如果 fmt 为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。
		
		例：SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,'day'),
		
		ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual;
      与 ROUND 对应的函数时 TRUNC(d[,fmt])对日期的操作， TRUNC 与 ROUND 非常相似，只是不对日期进行舍入，直接截取到对应格式的第一   天。

4、EXTRACT(fmt FROM d)，提取日期中的特定部分。

		fmt 为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。
		
		HOUR 匹配的结果中没有加上时区，因此在中国运行的结果小 8 小时。
		
		例：SELECT SYSDATE "date",
		
		       EXTRACT(YEAR FROM SYSDATE)"year",
		
		       EXTRACT(MONTH FROM SYSDATE)"month",
		
		       EXTRACT(DAY FROM SYSDATE)"day",
		
		       EXTRACT(HOUR FROM SYSTIMESTAMP)"hour",
		
		       EXTRACT(MINUTE FROM SYSTIMESTAMP)"minute",
		
		       EXTRACT(SECOND FROM SYSTIMESTAMP)"second"
		
		FROM dual;

5. 转换函数

转换函数将值从一种数据类型转换为另外一种数据类型。常见的转换函数有：

1、TO_CHAR(d|n[,fmt])

把日期和数字转换为制定格式的字符串。Fmt是格式化字符串

代码演示：TO_CHAR对日期的处理

SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS')"date" FROM dual;
代码解析：
在格式化字符串中，使用双引号对非格式化字符进行引用

针对数字的格式化，格式化字符有：

参数	示例	说明
9	999	指定位置处显示数字
.	9.9	指定位置返回小数点
,	99,99	指定位置返回一个逗号
$	$999	数字开头返回一个美元符号
EEEE	9.99EEEE	科学计数法表示
L	L999	数字前加一个本地货币符号
PR	999PR	如果数字式负数则用尖括号进行表示

如果数字式负数则用尖括号进行表示

代码演示：TO_CHAR对数字的处理

SELECT TO_CHAR(-123123.45,'L9.9EEEEPR')"date" FROM dual;

2、TO_DATE(X,[,fmt])

把一个字符串以fmt格式转换成一个日期类型

3、TO_NUMBER(X,[,fmt])

把一个字符串以fmt格式转换为一个数字

代码演示：TO_NUM函数

SELECT TO_NUMBER('-$12,345.67','$99,999.99')"num" FROM dual;
========================================
PLSQL添加环境变量：
变量名：NLS_LANG
变量值：SIMPLIFIED CHINESE_CHINA.ZHS16GBK
========================================