Oracle常用函数——持续更新中

19.oracle格式化包含“年.月.日”中文时间

SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日"') as 中文时间 FROM dual;

18.oracle列转行函数

SELECT REGEXP_SUBSTR ('1,2,3', '[^,]+', 1,rownum) from dual
connect by rownum<=LENGTH ('1,2,3') - LENGTH (regexp_replace('1,2,3', ',', ''))+1

17.cast函数和decimal函数

cast(字段名 as 转换的类型 )
decimal(3,1)，表示数值中共有3位数，小数占1位

select cast('1.19' as decimal(3,1)) as result from  dual;

16.根据出生日期计算年龄TRUNC

SELECT A0101 as name,A0107 as birthday,TRUNC(months_between(sysdate, A0107)/12) AS age FROM A01;

15.Oracle获取Date日期年、月、日（extract）

语法1：extract(year FROM Date类型日期)；
语法1：extract(month FROM Date类型日期)；
语法1：extract(day FROM Date类型日期)；

--方法1（extract函数）：
SELECT extract(year FROM T2203) AS 年份 FROM TAL_A22;
SELECT extract(month FROM T2203) AS 年份 FROM TAL_A22;
SELECT extract(day FROM T2203) AS 年份 FROM TAL_A22;

--方法2（常用）：
SELECT TO_CHAR(T2203,'yyyy') AS 年份 FROM TAL_A22;
SELECT TO_CHAR(T2203,'MM') AS 年份 FROM TAL_A22;
SELECT TO_CHAR(T2203,'dd') AS 年份 FROM TAL_A22;

14.Lpad和Rpad填充函数

Lpad函数：当字符串长度不够时，左填充补齐，可以指定补齐时用什么字符补齐,若不指定，则以空格补齐。
Rpad函数：当字符串长度不够时，右填充补齐，原理同左填充

-- 从左补充3个空格
SELECT '#'||Lpad('abc',6)||'#' FROM dual;

--从左补充3个*
SELECT '#'||Lpad('abc',6,'*')||'#' FROM dual;

--从右补充3个空格
SELECT '#'||Rpad('abc',6)||'#' FROM dual;

--从右补充3个*
SELECT '#'||Rpad('abc',6,'*')||'#' FROM dual;

执行结果：

13.initcap函数

initcap(ch)，将首字母大写。

SELECT initcap('abc') FROM dual
UNION ALL
SELECT initcap('zhang san') FROM dual
UNION ALL
SELECT initcap('li si') FROM dual;

执行结果：

12.nlssort排序函数:

Oracle9i之前，中文是按照二进制编码进行排序的，在oracle9i中新增了按照拼音、部首、笔画排序功能。

-- 按照拼音排序
SELECT A0101 FROM A01 
ORDER BY NLSSORT(A0101,'NLS_SORT=SCHINESE_PINYIN_M');

-- 按照部首排序
SELECT A0101 FROM A01 
ORDER BY NLSSORT(A0101,'NLS_SORT=SCHINESE_RADICAL_M');

-- 按照笔画排序
SELECT A0101 FROM A01 
ORDER BY NLSSORT(A0101,'NLS_SORT=SCHINESE_STROKE_M');

11.concat拼接函数

oracle中CONCAT(ch1,ch2)函数有两个参数，作用是拼接字符串，以逗号分隔。

SELECT CONCAT(CONCAT('前缀', '||'),'后缀') FROM dual;

10.wm_concat列转行函数

wm_concat(ch)函数有一个参数，作用是列转行。

10.1实例：

-- 普通查询sql（列）
SELECT A0101 FROM A01 WHERE ROWNUM < 6;

--列转行
SELECT wm_concat(A0101) FROM A01 WHERE ROWNUM < 6;

10.2执行sql前与执行sql后结果对比：

行转列前：

行转列后：

9.oracle大小写转换函数

-- 小写转大写
SELECT upper('abc') FROM dual;

--大写转小写
SELECT lower('ABC') FROM dual;

8.instr函数

instr(ch1,ch2)，判断ch1字符串中，是否包含ch2字符串。

--包含，则返回该字符串第一次出现的位置，
SELECT instr('abcd','ab') FROM dual;

--不包含，则返回0
SELECT instr('abcd','de') FROM dual;

7.replace函数

replace(ch1,ch2,ch3),将字符串ch1中出现的ch2字符串替换成ch3字符串。

SELECT replace('abcdabcefg','abc','#') FROM dual;

执行结果：

6.trim函数

trim(ch1)，去除字符串首尾两端的空格；
trim(ch from source)从字符串两侧去除指定的所有字符串；

-- 去除字符串首尾两端的空格
SELECT '#'||trim(' abcda  bc efg  ')||'#' FROM dual;

-- 去掉abcddcba字符串两端的a字符（只能去除首尾单个字符）
SELECT '#'||trim('a' from 'abadba')||'#' FROM dual;

执行结果：

5.Ltrim和Rtrim函数

Ltrim函数：
从字符串左侧去除指定的所有字符串，若没有指定去除的字符串，则默认去除左侧空白符；
Rtrim函数：
从字符串右侧去除指定的所有字符串，原理同Ltrim函数；

-- 原字符串
SELECT '#'||' abcda  bc efg  '||'#' FROM dual;

-- 去掉左侧空格字符串
SELECT '#'||Ltrim(' abcda  bc efg  ')||'#' FROM dual;

-- 去掉右侧空格字符串
SELECT '#'||Rtrim(' abcda  bc efg  ')||'#' FROM dual;

-- 去掉左侧' ab'（ab前一个空格，若不加空格，去不掉ab串）字符串
SELECT '#'||Ltrim(' abcda  bc efg  ',' ab')||'#' FROM dual;

-- 去掉右侧'efg  '（efg后两个空格，若不加空格，去不掉efg串）字符串
SELECT '#'||Rtrim(' abcda  bc efg  ','efg  ')||'#' FROM dual;

执行结果：

4.length和lengthb函数

-- 查看所占字符
SELECT length('张三'),length('a*b') FROM dual

-- 查看所占字节
SELECT lengthb('张三'),length('a*b') FROM dual;

执行结果：

3.nvl和nvl2函数

nvl(ch1,ch2)：参数ch1为null，返回ch2；
nvl2(ch1,ch2,ch3)：参数ch1不为null，返回ch1，为null，返回ch2；

SELECT nvl(null,0),nvl2(NULL,0,1),nvl2('1',0,1) FROM dual;

执行结果：

2.数值函数（abs、ceil、floor）

abs(ch1)：返回绝对值；
ceil(ch1)：返回大于等于ch1的最小整数；
floor(ch1)：返回小于等于ch1的最大整数；

-- abs函数
SELECT abs(-1),abs(0),abs(1) FROM dual;

-- ceil函数
SELECT ceil(-1.6),ceil(-0.1),ceil(1.1) FROM dual;

-- floor函数
SELECT floor(-1.6),floor(-0.1),floor(1.1) FROM dual;

执行结果：

1.round函数（四舍五入）

round(n1,n2)对n1进行四舍五入，保存小数点右侧的n2位。如果n2省略的话，相当于n2=0的情况；

-- 用法1：
SELECT round(-1.6),round(-1.5),round(-1.4) FROM dual
UNION ALL
SELECT round(1.6),round(1.5),round(1.4) FROM dual;

-- 用法2：
select round(555.666,-3),round(555.666,-2),round(555.666,-1) from dual
UNION ALL
select round(555.666,3),round(555.666,2),round(555.666,1) from dual;

执行结果：