【SQL学习笔记】单行函数

单行函数的特征

• 单行函数对单行操作
• 每行返回一个结果
• 有可能返回值与原参数数据类型不一致
• 单行函数可以写在SELECT、WHERE、ORDER BY子句中
• 有些函数没有参数，有些函数包括一个或多个参数
• 函数可以嵌套

一、字符函数

1、大小写控制函数

LOWER('SQL Course'):结果为 sql course

UPPER('SQL Course'):结果为 SQL COURSE

INITCAP('SQL Course'):结果为 Sql Course

2、字符控制函数

CONCAT('Hello','World'):结果为 HelloWorld

SUBSTR('HelloWorld',1,5):结果为 Hello

LENGTH('HelloWorld'):结果为 10

INSTR('HelloWorld','W'):结果为 6

LPAD('Hello',10,'*'):结果为 *****Hello

RPAD('Hello',10,'*'):结果为 Hello*****

TRIM('H' FROM 'HelloWorld'):结果为 elloWorld

REPLACE('Hello','e','*'):结果为 H*llo

二、数字函数

四舍五入：

ROUND(45.926,2):结果为 45.93
ROUND(45.926,0):结果为 46,defult为 0
ROUND(45.926,-1):结果为 50

截断：

TRUNC(45.926,2):结果为 45.92
TRUNC(45.926,0):结果为 45
TRUNC(45.926,-2):结果为 0

求余：

MOD(11000,5000):结果为 1000

三、日期函数

两个日期相差的月数：

MONTHS_BETWEEN('01-SEP-95','11-JAN-94'):结果为 19.6774194

向指定日期中加上若干月数：

ADD_MONTHS('11-JAN-94',6):结果为 11-JUL-94

指定日期的下一个星期__对应的日期：

NEXT_DAY('01-SEP-95','FRIDAY'):结果为 08-SEP-95

本月的最后一天：

LAST_DAY('01-FEB-95'):结果为 28-FEB-95

日期四舍五入：

ROUND('25-JUL-95','MONTH'):结果为 01-AUG-95
ROUND('25-JUL-95','YEAR'):结果为 01-JAN-96

日期截断：

TRUNC('25-JUL-95','MONTH'):结果为 01-JUL-95
TRUNC('25-JUL-95','YEAR'):结果为 01-JAN-95

四、转换函数

1、隐式数据类型转换

• Oracle自动完成下列转换：

• VARCHAR2 or CHAR ⇨ NUMBER

• VARCHAR2 or CHAR ⇨ DATE

• NUMBER ⇨ VARCHAR2

• DATE ⇨ VARCHAR2

2、显示数据类型转换

• TO_CHAR函数对日期的转换:

TO_CHAR(date, 'format_model'):将date转换为'format_model'的格式

format_model中的格式:

符号	结果
YYYY	2004
YEAR	TWO THOUSAND AND FOUR
MM	02
MONTH	JULY
MON	JUL
DY	MON
DAY	MONDAY
DD	02
HH24:MI:SS AM	15:45:32 PM

• TO_CHAR函数对数字的转换:

TO_CHAR(number,'format_model'):将number转换为'format_model'的格式

format_model中的格式:

符号	结果
9	数字
0	零
$	美元符
L	本地货币符号
.	小数点
,	千位符

• TO_NUMBER函数对字符的转换:

TO_NUMBER(char[,'format_model']):使用TO_NUMBER函数将字符转换成日期
TO_NUMBER('$1,234','L99,999')

五、通用函数

• NVL (expr1, expr2): expr1为NULL，返回expr2。
• NVL2 (expr1, expr2, expr3): expr1不为NULL，返回expr2；为NULL，返回expr3。
• NULLIF (expr1, expr2): 相等返回NULL，不等返回expr1。
• COALESCE (expr1, expr2, …, exprn): 如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE。

条件表达式

CASE表达式:

CASE expr WHEN comparison_expr1 THEN return_expr1
         [WHEN comparison_expr2 THEN return_expr2
          WHEN comparison_exprn THEN return_exprn
          ELSE else_expr]
END

DECODE函数:

DECODE(col|expression, search1, result1 ,
           [, search2, result2,...,]
           [, default])