</p> 19. 求集合最小值- p: y; ^" K& u' Y
S:select min(value) value from2 P& D1 n8 F; D) y1 x/ P) L
(select 1 value
, b0 Z/ Y$ c- j union1 P# ~% i2 k2 o% i4 \6 t9 d
select -2 value3 j0 F4 H& G+ E" L. e! u
union6 _5 W6 a$ J3 [5 H/ ]
select 4 value0 b$ c, |- M, z; ^0 ^- P
union
8 n+ j$ O+ A6 M s: u/ Q select 3 value)a
2 q+ y0 z( W" z+ F% E) D) N O:select least(1,-2,4,3) value from dual% K/ }' u! G. j1 M B u3 Y9 ?
20.如何处理null值(F2中的null以10代替)
% G2 I9 b; C4 l S:select F1,IsNull(F2,10) value from Tbl
) U {2 ^( _& _$ y O:select F1,nvl(F2,10) value from Tbl
5 E/ h' {1 R6 t 21.求字符序号- `: \8 @4 @; ^* H
S:select ascii('a') value! i! D0 I0 `0 t3 h/ e
O:select ascii('a') value from dual2 [* d2 j' s" c0 \
22.从序号求字符
4 s$ O/ } |5 L: @" z- G) h% |: C S:select char(97) value: Q% }2 i8 B% D3 }
O:select chr(97) value from dual3 p4 c$ Q r H
23.连接0 @2 S& q+ d. Z4 F
S:select '11'+'22'+'33' value
! h1 |* N7 ~( H; o- z8 S8 a O:select CONCAT('11','22') 33 value from dual
) x3 M+ T' f+ m' _9 @, B9 } 23.子串位置 --返回3( n$ W& g3 d& m' `$ q* w& i
S:select CHARINDEX('s','sdsq',2) value
) r+ Y: c* ~' u, t O:select INSTR('sdsq','s',2) value from dual0 P- Z4 l0 v& u* [! G7 ~- ~2 _+ J5 c
23.模糊子串的位置 --返回2,参数去掉中间%则返回7
# p' b6 @ n6 E7 m/ W S:select patindex('%d%q%','sdsfasdqe') value, ^; \" ` ^0 _! _2 T* k2 a" K5 j3 z
O:oracle没发现,但是instr可以通过第四个参数控制出现次数
% H& N+ ]9 C/ t! l# @# ` select INSTR('sdsfasdqe','sd',1,2) value from dual 返回6$ k. q4 F/ X+ o9 N9 V9 {
24.求子串; Z( }& u' r! [6 T+ E
S:select substring('abcd',2,2) value
+ V J' o; P$ t2 z; \ O:select substr('abcd',2,2) value from dual
! s5 L, G# Q% S 25.子串代替 返回aijklmnef% t' n! L% H; a* S5 ^+ t
S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value
' j/ ~6 w: M. l4 g* n5 u O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual |