</p> 19. 求集合最小值: f4 m0 S! q! Q0 C: _- D7 Y. h
S:select min(value) value from3 w8 g5 o1 t; u3 M- A8 \
(select 1 value
5 f, z8 Z. E9 j union; C/ i# J. O; b2 o, W
select -2 value$ Q7 U- x6 T5 Z8 D$ h" n
union
& D9 z; `6 h9 G4 Q4 t3 z select 4 value
, l$ N- S" V9 E' ?8 ]% ^. i+ q union( f- [, u- Z5 n8 F) e' x
select 3 value)a
/ L: w1 B z+ C0 W% i O:select least(1,-2,4,3) value from dual
- L4 g B4 j Z, [( B1 B" } 20.如何处理null值(F2中的null以10代替)
" ]. l. F' s( N# R S:select F1,IsNull(F2,10) value from Tbl
. H9 ?; h( X2 s7 S* J! y& B O:select F1,nvl(F2,10) value from Tbl# i9 w5 G" V. a# b- y1 L. R0 i
21.求字符序号2 S. _9 s2 q0 ?
S:select ascii('a') value9 W- h! E: ]+ L+ r
O:select ascii('a') value from dual
7 ?! b+ O5 e4 i, g# `+ [ 22.从序号求字符
2 M d4 }' ?5 s. V S:select char(97) value
& n' D+ ~+ B/ l3 h1 \ O:select chr(97) value from dual
( P4 o5 b1 c' P4 g: Y2 D 23.连接! V( I3 f8 W/ b3 `( ~/ X1 u* H
S:select '11'+'22'+'33' value
6 H% E0 \% w' m9 T9 Y O:select CONCAT('11','22') 33 value from dual
4 M/ p, _ [4 F# p! r 23.子串位置 --返回3
$ J: x/ v" a( N( O! B S:select CHARINDEX('s','sdsq',2) value
& F ~* X" x' ~ O:select INSTR('sdsq','s',2) value from dual I. A6 H3 E/ j. W
23.模糊子串的位置 --返回2,参数去掉中间%则返回7: \, e2 T) B1 O" }* P
S:select patindex('%d%q%','sdsfasdqe') value& t& C! c7 o+ d9 Q4 B
O:oracle没发现,但是instr可以通过第四个参数控制出现次数) G( {; q7 l. G+ l( X
select INSTR('sdsfasdqe','sd',1,2) value from dual 返回6" U( i( j5 b/ Z( r
24.求子串
! ~4 i+ q; C Y+ ?4 q+ T S:select substring('abcd',2,2) value
9 m* }8 j0 ], F( B O:select substr('abcd',2,2) value from dual
& ~+ n) z- c3 X5 O 25.子串代替 返回aijklmnef
0 l3 |4 @# w% C" F' |8 b0 V6 e2 E0 V S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value9 m9 @8 \% ~* f
O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual |