Oracle字串處理
字串處理
1.INSTR 找尋字串所在位置
INSTR(string1, string2,[, n[ ,m]])
select INSTR('true blood' , 'blood') from dual
6
2. LENGTH 取得字串長度
LENGTH(string)
select length('true blood') from dual
10
3.REPLACE字串取代
REPLACE(string, search_string, [,replacement_string])
select replace('Bill,I love you','Bill','Sookie') from dual
Sookie,I love you
4.LPAD字串左邊補上特定字元
LPAD(char1,n,char2)
select lpad('Bill', 8, '0') from dual;
0000Bill
RPAD ()在字串右邊補上特定字元
5.字串的截取(從後面或前面取)
substr (string string, int start [, int length])
select substr('This is Bill.', 9, 4) from dual
Bill
6.只取欄位中長度為5碼之字串
select column_b from table_a where column_b like '_____'
