oracle字串函數

標籤：

1. replace，translate

--translate 字元層級的替換--replace 字串層級的替換select replace(‘abaabbb‘,‘ab‘,‘c‘) from dual;--cacbbselect translate(‘aaabbb‘,‘ab‘,‘ce‘) from dual;--ccceee

 

2. concat 字串串連，等同於 || 

select concat(‘test‘, ‘_concat‘) from dual;--test_concatselect ‘test‘ || ‘_concat‘ from dual; --test_concat

 

3. upper,lower,initcap  指定語言集的方法：NLS_UPPER,NLS_LOWER,NLS_INITCAP

select upper(‘AaaaAa‘) from dual; --AAAAAAselect lower(‘AaaaAa‘) from dual; --aaaaaaselect initcap(‘aaaaa‘) from dual; --Aaaaaselect initcap(‘aaa aaa‘) from dual; --Aaa Aaa

 

4.lpad,rpad  字串填充，如果不指定填充字元，預設為空白格

select lpad(‘aa‘,10,‘0‘) from dual; --左填充  00000000aaselect rpad(‘aa‘,10,‘0‘) from dual; --右填充  aa00000000

 

5. ltrim,rtrim,trim(leading,trailing,both) 字串修剪，trim只支援單字元修剪？ 預設剪去空格

select ltrim(‘111111111000123000‘,‘01‘) from dual; --從左截掉所有0和1 23000select rtrim(‘00012300011111‘,‘012‘) from dual;----從右截掉所有0和1 000123select trim(leading ‘0‘ from ‘000123000‘) from dual;  --從頭截掉所有的0 123000select trim(trailing ‘0‘ from ‘000123000‘) from dual;  --從尾截掉所有的0 000123select trim(both ‘0‘ from ‘000123000‘) from dual;  --兩邊同時截掉所有的0 123

 

6. substr

--substr(char,start,length) 截取char從start開始的長度為length的字串select substr(‘abc‘,0,1) from dual; --返回aselect substr(‘abc‘,1,1) from dual;--start為0和start為1效果相同select substr(‘abcdefgs‘,-5,3) from dual;--如果start為負數，則從倒數第start個字元開始截取  返回defselect substr(‘abcdefgs‘,-5) from dual;--如果省略length，則截取start直到結尾的字串  返回defgs

 

7. regexp_substr,regexp_replace

 

oracle字串函數