常用function 一(數字、字串) (DML 二)

官網連結

※數字function

SELECT abs(-212),power(2,10),sqrt(25),sign(6) FROM dual;--1
    
--2
ceil(67.84)：68
ceil(-67.84)：-67
    
floor(67.84)：67
floor(-67.84) ：-68
    
--3
round(15.193,1)：15.2
round(15.193,2)：15.19
round(15.193,0)：15
round(15.193)：15
round(15.193,-1)：20
round(15.193,-2)：0
    
--4
trunc(15.193,1)：15.1
trunc(15.193,2)：15.19
trunc(15.193,0)：15
trunc(15.193)：15
trunc(15.193,-1)：10
trunc(15.193,-2)：0
    
--5
mod(11,4)：3
mod(-11,-4)：-3
mod(-11,4)：-3
mod(11,-4)：3

※1：abs是絕對值；power是次方，範例是2的10次方；sqrt是根號，5*5=25，所以是5．
sign裡面放數字，正數回傳1，負數回傳-1，0回傳0

※2：天花板與地板，都是以整數為主(不管小數)，往上或往下拉

※3：四捨五入，第二個參數是小數第幾位，0可以省略

※4：取到小數第幾位，0可省略

※5：取餘數，正負數以第一個參數為主

※一串數字，取最大和最小

select greatest(1,2,3,4,4.5) max, least(1,2,3,4,0.5) min from dual;

※max 為4.5；min 為0.5

※字串function

--6
chr(48)：0
chr(65)：A
chr(97)：a
chr(65+256)：A
    
--7
ascii('0')：48
ascii('a')：97
ascii('A')：65
ascii('05')：48
ascii(01)：49
    
--8
SELECT
     concat('xxx', 'ooo') -- xxxooo
    ,concat(dname,'ooo') -- RESEARCHooo
    ,concat('ooo',dname) -- oooRESEARCH
    ,dname||'ooo' -- RESEARCHooo
FROM dept where deptno=20;
    
--9
replace('back','b','h')：hack
initcap('monkey')：Monkey
upper('moNkey')：MONKEY
lower('moNkey')：monkey
translate(56338, '123456789', 'avlihemoqr')：hello
    
--10
trim('  a  ')：a
ltrim('  a  ')：a  
rtrim('  a  ')：  a
length('ab')：2
length(ltrim('  a  '))：3
    
--11
lpad('a',5,'#')：####a
rpad('a',5,'#')：a####
    
--12
substr('ABCDEFG',3,4)：CDEF
substr('ABCDEFG',-5,4)：CDEF
substr('ABCDEFG',3,4.2)：CDEF
substr('ABCDEFG',3,4.6)：CDEF
    
--13
instr('corporate floor','or')：2
instr('corporate floor','or',1,1)：2
instr('corporate floor','or',1,3)：14
instr('corporate floor','or', 1, 2)：5
instr('corporate floor','or', 2, 2)：5
instr('corporate floor','or', 3, 2)：14
instr('corporate floor','or', -1, 3)：2

※6：裡面的數字是ASCII code，45、65、97是一定要背的，超過2的8次方(也就是256)，會一直減256，減到256之內

※7：和chr相反，如果裡面放字串，以第一個為主；如果是數字，如範例是01，那就是1
如果想知道「'」的ascii，可以用跳脫字元「'」，所以是ascii('''');為39

※8：concat和「||」一樣，都是串接，java也有，可搭配欄位，以下的function都一樣

※9：取代、首字大寫、全大寫、全小寫
translate第二和第三個參數是比對用的，如1=a,2=v,3=l…等，不一定要用數字，如以下的寫法，結果也是一樣「translate('efcch', 'abcdefghi', 'avlihemoqr')」

※10：left、right的trim，看要往那邊去除空白或兩邊都去掉，可搭配length確定有沒有成功

※11：範例的意思是，a有5個空格，往左或右增加「#」，要小心中文問題

※12：最左邊是1，範例的意思是從第3個字元取4個字(中文也算一個字)
最右邊是-1，所以第二個的結果也是一樣，第三和第四個不管小數點，所以也和第一個一樣

※13：第3和第4個參數不寫，就都是1
第3個參數表示從第幾個字元開始尋找；第4個參數表示出現第幾次
如果第3個參數是負的，表示從右往左尋找