一、数值型函数
1、绝对值、取余、判断数值正负函数
1)ABS(n)函数
2)MOD(n2,n1)函数
3)SIGN(n)函数
2、三角函数
3、返回以指定数值为准整数的函数
1)CEIL函数----返回大于等于输入参数的最小整数
SELECT CEIL(10),CEIL('10.5'),CEIL(-10.2) FROM DUAL; ------10 11 -10
2)FLOOR(N)函数---返回小于等于输入参数的最大整数
SELECT FLOOR(10),FLOOR('10.5'),FLOOR(-10.2) FROM DUAL; --10 10 -11
4、指数、对数函数
1)SQRT(n)函数
SELECT SQRT(100),SQRT('49') FROM DUAL; --10 7
2)POWER(n2,n1)函数
SELECT POWER(5,2),POWER('5',2),POWER(-5,2) FROM DUAL; --25 25 25
3)LOG(n1,n2)函数
SELECT LOG(10,100),POWER(10,2) FROM DUAL; --2 100
5、四舍五入截取函数
SELECT ROUND(100.23456,4),ROUND(100.23456,2.56),ROUND(155.23456,-2) FROM DUAL; --100.2346 100.23 200
SELECT TRUNC(100.23456,4),TRUNC(100.23456,2.56),TRUNC(155.23456,-2),TRUNC(155.23456) FROM DUAL;
------100.2345 100.23 100 155
二、字符型函数
1、ASCII码与字符转换函数
2.获取字符串长度函数
SELECT LENGTH('ABCDE我FGHI') FROM DUAL; --10
3.字符串截取函数
SELECT SUBSTR('ABCDE我FGHI',5,2),SUBSTR('ABCDE我FGHI',-5,2) FROM DUAL;
----E我 我E
4.字符串连接函数
SELECT CONCAT('我的',‘测试’),'我的'||'测试' FROM DUAL;
5.字符串搜索函数
SELECT INSTR('this is a 测试!','测'),INSTR('this is a 测试!','s',-1) FROM DUAL;
----11 7
6.字母大小写转换函数
SELECT UPPER('c'),UPPER('abcd'),UPPER('this is a test') FROM DUAL;
-----C ABCD THIS IS A TEST
SELECT LOWER('C'),LOWER('ABCD'),LOWER('THIS IS A TEST') FROM DUAL;
-----c abcd this is a test
SELECT INITCAP('this is a test') FROM DUAL;
----This Is A Test;
7、带排序参数的字母大小写转换函数
SELECT NLS_INITCAP('a test'),NLS_INITCAP('my test','NLS_SORT=SCHINESE_STROKE_M') FROM DUAL;
--A Test My Test
SELECT NLS_UPPER('this is a test','NLS_SORT=SCHINESE_PINYIN_M') FROM DUAL;
--THIS IS A TEST;
SELECT NLS_LOWER('ABC','NLS_SORT=XGerman'),NLS_LOWER('THIS IS A TEST','NLS_SORT=XGerman') FROM DUAL;
---abc this is a test
8、为指定参数排序函数
SELECT * FROM PRODUCTINFO ORDER BY NLSSORT(PRODUCTNAME,'NLS_SORT=SCHINESE_PINYIN_M');
----根据productname字段按拼音排序。
9、替换字符串函数
SELECT REPLACE('this is a test','tes','resul') FROM DUAL;
10、字符串填充函数
SELECT RPAD('test',8,'*rpad'),RPAD('test',15,'*rpad'),RPAD('test',4,'*rpad') FROM DUAL;
---test*rpa test*rpad*rpad* test
SELECT LPAD('test',8,'*rpad'),LPAD('test',15,'*rpad'),LPAD('test',4,'*rpad') FROM DUAL;
---- *rpatest *rpad*rpad*test test
11、删除字符串首尾指定字符的函数
SELECT TRIM(TRAILING 't' FROM 'test'),TRIM(' test ') FROM DUAL;
----tes test
SELECT RTRIM('test '),RTRIM('test*ffs','fs*') FROM DUAL;
----trim trim
SELECT LTRIM(' ftest',' f') FROM DUAL;
----test
12、字符集名称和ID互换函数
SELECT NLS_CHARSET_ID('US7ASCII') FROM DUAL;
----- 1
SELECT NLS_CHARSET_NAME(1) FROM DUAL;
------- US7ASCII