三。 字符函数(可用于字面字符或数据库列)
/ }& l& k) [2 Z! h9 Z, i 1,字符串截取! U- {" H( z# n
select substr('abcdef',1,3) from dual
5 B) e( a) F8 M1 y6 E 2,查找子串位置9 z) X; l1 U" U# j9 r, M
select instr('abcfdgfdhd','fd') from dual
- z& _& F! M# H7 e6 n* j 3,字符串连接, f7 x, V/ q( q9 j
select 'HELLO'||'hello world' from dual;
9 [! u) C- z& e3 N0 ] 4, 1)去掉字符串中的空格; f b, w, V! o7 {. ^% `& J
select ltrim(' abc') s1," ` _4 g! P1 V7 G/ i: ?/ U, ]
rtrim('zhang ') s2,$ q, X7 |9 H9 ?( x. L n c7 I
trim(' zhang ') s3 from dual
$ @/ `0 R/ O8 f2 r1 q1 K; i5 I 2)去掉前导和后缀9 U& k* X1 l: t+ K9 l% F x
select trim(leading 9 from 9998767999) s1,
! \! X7 G) c! U+ [$ F; ~ P trim(trailing 9 from 9998767999) s2,- D: d6 h1 G2 C4 I/ s
trim(9 from 9998767999) s3 from dual;
+ g1 M0 M e. S* ^- ? 5,返回字符串首字母的Ascii值/ g9 L3 I- ? N# P' N* M
select ascii('a') from dual: h8 |2 I' J: ?9 M+ f. k
6,返回ascii值对应的字母
1 P% f1 I1 V5 ?5 A$ ~2 S, p+ \" ^5 Y select chr(97) from dual- W( }& y% N3 g: F# ^! d
7,计算字符串长度
# V6 N {. i7 e9 \+ \3 @ select length('abcdef') from dual4 _( f; u: t" r% C
8,initcap(首字母变大写) ,lower(变小写),upper(变大写)/ r2 X; F& u3 N! G
select lower('ABC') s1,
* v9 q8 M B, E upper('def') s2,2 _, m, P& b A9 Y r1 F0 g( B. U
initcap('efg') s3+ t9 t: t9 |3 _9 t
from dual;+ ^ Y k& B, v5 B! h
9,Replace: I+ k: W: v- r/ j
select replace('abc','b','xy') from dual;4 H" {# D% x0 ?! `0 u
10,translate
1 f/ ~* y; Z. x9 \ c select translate('abc','b','xx') from dual; -- x是1位$ C# U" q+ p5 n$ i3 o2 ^
11,lpad [左添充] rpad [右填充](用于控制输出格式)
8 v! J6 l8 o" F) h* ~ select lpad('func',15,'=') s1, rpad('func',15,'-') s2 from dual;$ @# _/ H% q1 f* O" P6 ?' N
select lpad(dname,14,'=') from dept;6 T' O. z W- P8 [
12, decode[实现if then 逻辑] 注:第一个是表达式,最后一个是不满足任何一个条件的值1 _% j) w1 m1 I/ J
select deptno,decode(deptno,10,'1',20,'2',30,'3','其他') from dept;
1 e* f2 M; h3 B6 i+ G 例:
7 a* Y7 n& `, b4 g% a select seed,account_name,decode(seed,111,1000,200,2000,0) from t_userInfo//如果seed为111,则取1000;为200,取2000;其它取0: P9 t/ L7 A$ X \
select seed,account_name,decode(sign(seed-111),1,'big seed',-1,'little seed','equal seed') from t_userInfo//如果seed>111,则显示大;为200,则显示小;其它则显
* A3 W) p( r* ^+ k. r6 v2 t 示相等
9 V2 m f; ?" [) K- f- U 13 case[实现switch case 逻辑]
$ ?. I# S2 K4 y( Y6 z/ ~$ B# u SELECT CASE X-FIELD8 \& c4 I5 s2 u. k: y
WHEN X-FIELD < 40 THEN 'X-FIELD 小于 40'
3 t8 s4 K3 N* j4 D WHEN X-FIELD < 50 THEN 'X-FIELD 小于 50': N! e: W8 B* v9 R! I6 _) _" K: U
WHEN X-FIELD < 60 THEN 'X-FIELD 小于 60'
* F! y2 i" q/ I$ z$ R ELSE 'UNBEKNOWN'
" E( x/ L% h% i6 L2 M; q/ S END
: _* B x( `# p( W FROM DUAL& n- M2 p/ f3 a; t# |/ G2 B
注:CASE语句在处理类似问题就显得非常灵活。当只是需要匹配少量数值时,用Decode更为简洁。6 |, r! o% P, `; W3 _! Z
四。数字函数
/ F0 M1 P9 d& Y) E; [: L0 j& z* K 1,取整函数(ceil 向上取整,floor 向下取整)' X/ w3 H2 P8 o9 x4 j) V) M
select ceil(66.6) N1,floor(66.6) N2 from dual;2 L* X2 R0 o. [+ m
2, 取幂(power) 和 求平方根(sqrt)
6 K# y a' x2 ^) C0 B' m3 B$ v1 z8 ^ select power(3,2) N1,sqrt(9) N2 from dual;
$ ^( t4 S* h' l; ^$ G0 D/ C 3,求余
( S% @2 `- M/ U# y! `5 ` select mod(9,5) from dual;3 M) ~( g* b' H O4 a
4,返回固定小数位数 (round:四舍五入,trunc:直接截断)
" a+ P% u$ w' q- d7 j0 _( r7 y6 s select round(66.667,2) N1,trunc(66.667,2) N2 from dual;# K: f* a& _; S* j
5,返回值的符号(正数返回为1,负数为-1)( M* U9 {' o) ?0 ^# x* B
select sign(-32),sign(293) from dual;1 a# s8 W1 o7 G* L, r( W
五。转换函数5 G/ w7 e5 a! h' N, H9 Y/ r/ t: [
1,to_char()[将日期和数字类型转换成字符类型]
6 W7 b! e2 Y0 I' G& ] U/ J$ ~0 T% U 1) select to_char(sysdate) s1,& c- J" C# M0 u) b
to_char(sysdate,'yyyy-mm-dd') s2,
: A z/ E2 M$ b2 V3 q# S to_char(sysdate,'yyyy') s3,
) h ~- v9 l2 _" Z to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') s4,
5 W& s* u1 W N, l9 B to_char(sysdate, 'hh24:mi:ss') s5,0 _! v' T, U& O/ ^2 L
to_char(sysdate,'DAY') s6
& @" C( M4 {1 K from dual;/ P$ i4 [9 \1 C
2) select sal,to_char(sal,'$99999') n1,to_char(sal,'$99,999') n2 from emp$ \+ s4 ?7 }' b0 _# q. b4 C
2, to_date()[将字符类型转换为日期类型]
5 |/ O. r* i1 C insert into emp(empno,hiredate) values(8000,to_date('2004-10-10','yyyy-mm-dd'));7 [& Q t8 H7 z3 g9 C% J. W4 y
3, to_number() 转换为数字类型( I- G: f$ a6 t/ X) [- i. w! }
select to_number(to_char(sysdate,'hh12')) from dual; //以数字显示的小时数
! K6 o8 j$ |: L$ d7 j 六。其他函数
# {* x# @) O9 ~. R 1.user:
! m* J+ D+ h0 Z f# D 返回登录的用户名称
. P- g3 k9 [" d% l0 v& t select user from dual;
N8 m. o3 b# z- Q. q( {9 h, {4 A 2.vsize:- ]3 C* }7 w) `' c% M- B5 z2 p
返回表达式所需的字节数
6 ?$ ^# x6 x0 _2 Q9 c( ] select vsize('HELLO') from dual;
% K' w8 \& J4 C% e p4 p: r 3.nvl(ex1,ex2):
$ m: f4 s! v$ v: C: w ex1值为空则返回ex2,否则返回该值本身ex1(常用)5 b# K/ @; Q1 ~$ j3 A2 o
例:如果雇员没有佣金,将显示0,否则显示佣金
4 J; A! J& g) n8 ?6 @ select comm,nvl(comm,0) from emp;' P( O1 S; K9 P8 [: S) P _0 j0 ^, z
4.nullif(ex1,ex2):9 c3 O7 {/ L" {" `7 l$ K9 j. g' e- n
值相等返空,否则返回第一个值 k" }) D$ }. T w) M$ G/ k
例:如果工资和佣金相等,则显示空,否则显示工资
$ z+ I& Q% h' l6 j' Q select nullif(sal,comm),sal,comm from emp;$ q. y. B& {$ C! P
5.coalesce:& I4 v; ` l2 @' H! y
返回列表中第一个非空表达式# k& k# R" o# S2 p% R& t* T& e S. J
select comm,sal,coalesce(comm,sal,sal*10) from emp;
- ~# f8 D# t% l6 z4 O; g# c 6.nvl2(ex1,ex2,ex3) :4 Z- {) b/ d4 m2 e; L
如果ex1不为空,显示ex2,否则显示ex3# x9 b; @8 S4 a5 r! D2 m; k- r/ c
如:查看有佣金的雇员姓名以及他们的佣金! [5 [$ q& ]/ \) \) ]; h
select nvl2(comm,ename,') as HaveCommName,comm from emp |