Oracle_函数_01

最新推荐文章于 2024-03-17 13:16:34 发布

云霄2016

最新推荐文章于 2024-03-17 13:16:34 发布

阅读量346

点赞数

分类专栏： Database

本文链接：https://blog.csdn.net/dearzqy/article/details/11563601

版权

Database 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

Oracle_Function

int INSTR(String, String)
int INSTR(String, String, int beginIndex)
int INSTR(String, String, int beginIndex, int appear)
/* SELECT VSIZE('中'), LENGTH('中'), LENGTHB('中') FROM dual; 2-1-2*/
int LENGTH(String) /* 字符数 */
int LENGTHB(String) /* 字节数 */
int VSIZE(String) /* 字节数 */
int ASCII(String) /* 字符串 str[1] 第一个的ascii值 */

String USERENV(String key)

String CHR(int)
String LOWER(String)
String UPPER(String)
String INITCAP(String)
String CONCAT(String, String)

String LPAD(String, int padded_len) /* 填充空格 padded_len: 填充之后返回的长度 */
String LPAD(String, int padded_len, String pad_str) /* 填充pad_str */
String RPAD(String, int padded_len)
String RPAD(String, int padded_len, String pad_str)

String TRIM(String) /* 去掉前后2边的空格默认是 both / 空格 */
String TRIM([LEADING | TRAILING | BOTH] String FROM String)
Number TRIM([LEADING | TRAILING | BOTH] Number FROM Number)
示例:
SELECT TRIM(LEADING 0 FROM 0005480000) FROM dual; /* 5480000 */
SELECT TRIM(BOTH 0 FROM 0005480000) FROM dual; /* 548 */
SELECT TRIM(TRAILING 0 FROM 0005480000) FROM dual; /* 548 数字-自动将前置0去掉 */
SELECT TRIM(TRAILING '0' FROM '0005480000') FROM dual; /* 000548 */

String LTRIM(String) /* 去掉左边的空格 */
String LTRIM(String, String trim)
String RTRIM(String) /* 去掉右边的空格 */
String RTRIM(String, String trim)
String SUBSTR(String, int begin)
String SUBSTR(String, int begin, int len)
String REPLACE(String, String oldSubstr, String newSubstr)

Number ABS(Number)
Number AVG([ALL | DISTINCT] List<Number> col)
Number MAX([ALL | DISTINCT] List<Number> col)
Number MIN([ALL | DISTINCT] List<Number> col)
Number SUM([ALL | DISTINCT] List<Number> col)
---------
int CEIL(Numebr)
示例:
SELECT CEIL(11.025) FROM dual; /* 12 */
SELECT CEIL(11.00) FROM dual; /* 11 */
SELECT CEIL(11) FROM dual; /* 11 */
SELECT CEIL(-11.908) FROM dual; /* -11 */

int FLOOR(Number)
示例:
SELECT FLOOR(11.908) FROM dual; /* 11 */
SELECT FLOOR(11.00) FROM dual; /* 11 */
SELECT FLOOR(11) FROM dual; /* 11 */
SELECT FLOOR(-11.908) FROM dual; /* -12 */
---------
int ROUND(Number) /* 四舍五入 0 位小数 */
Number ROUND(Number, int)
示例: /* 正负数，四舍五入值进行取舍 */
SELECT ROUND(12.54847) FROM dual; /* 13 */
SELECT ROUND(12.34847) FROM dual; /* 12 */
SELECT ROUND(-11.525) FROM dual; /* -12 */
SELECT ROUND(12.54847, 2) FROM dual; /* 12.55 */
SELECT ROUND(12.54847, 3) FROM dual; /* 12.548 */
SELECT ROUND(12.54847, -1) FROM dual; /* 10 */
SELECT ROUND(15.54847, -1) FROM dual; /* 20 */
SELECT ROUND(12.54847, -2) FROM dual; /* 0 */