ORACLE單行函式與多行函式之二：字元函式示例

1.大小寫轉換

initcap(char) 把每個字串的首字元換成大寫

lower(char) 整個字串轉換成小寫

upper(char) 整個字串轉換成大寫

BYS@bys1>Select lower(ename),upper(ename),initcap(ename) from emp where rownum<3;
LOWER(ENAM UPPER(ENAM INITCAP(EN
---------- ---------- ----------
smith      SMITH      Smith
allen      ALLEN      Allen

2.字元連線CONCAT和||

CONCAT (char1, char2)    連線char1和char2，注意：CONCAT只能連線兩個字串，連線三個將報錯。

BYS@bys1>select ename,job,ename,concat(ename,job) from emp where rownum<3;
ENAME      JOB       ENAME      CONCAT(ENAME,JOB)
---------- --------- ---------- -------------------
SMITH      CLERK     SMITH      SMITHCLERK
ALLEN      SALESMAN  ALLEN      ALLENSALESMAN
BYS@bys1>select ename,job,ename,concat(ename,job,ename) from emp where rownum<3;
select ename,job,ename,concat(ename,job,ename) from emp where rownum<3
                       *
ERROR at line 1:
ORA-00909: invalid number of arguments

||連線符，一大優勢是可以在連續符中間新增指定的字元

BYS@bys1>select ename,job,ename,ename||ename||job from emp where rownum<3;
ENAME      JOB       ENAME      ENAME||ENAME||JOB
---------- --------- ---------- -----------------------------
SMITH      CLERK     SMITH      SMITHSMITHCLERK
ALLEN      SALESMAN  ALLEN      ALLENALLENSALESMAN

BYS@bys1>select ename,job,ename,'mingzi '||ename||ename||'  gongzuo  '||job from emp where rownum<3;
ENAME      JOB       ENAME      'MINGZI'||ENAME||ENAME||'GONGZUO'||JOB
---------- --------- ---------- -----------------------------------------------
SMITH      CLERK     SMITH      mingzi SMITHSMITH  gongzuo CLERK
ALLEN      SALESMAN  ALLEN      mingzi ALLENALLEN  gongzuo  SALESMAN

3.求字串的長度;length  字串要單引

 length--按照字,可以求字串長度，也可以求數字的長度

 lengthb--按位元組

 lengthc--unicode的長度

BYS@bys1>select ename,length(ename),sal,length(sal) from emp where rownum<5;
ENAME      LENGTH(ENAME)        SAL LENGTH(SAL)
---------- ------------- ---------- -----------
SMITH                  5        800           3
ALLEN                  5       1600           4
WARD                   4       1250           4
JONES                  5       2975           4

SQL> select length('白不'),length('bai'), lengthb('白不'),lengthb('bai'),lengthc('白不'),lengthc('bai')  from dual;
  LENGTH('白不') LENGTH('BAI')   LENGTHB('白不') LENGTHB('BAI')   LENGTHC('白不') LENGTHC('BAI')
-------------- ------------- --------------- -------------- --------------- --------------
             2             3               6              3              2              3

4.擷取，SUBSTR(string,start,count) 從start開始，擷取count個。

如果不指定count，則預設擷取到最後一個字元。如果start為負數，則從倒數第N個字元擷取。
BYS@bys1>select substr('15912345678',4,8),substr('15912345678',4),substr('15912345678',-8,8) from dual;
SUBSTR('15912345678',4,8) SUBSTR('15912345678',4) SUBSTR('15912345678',-8,8)
------------------------- ----------------------- --------------------------
12345678                  12345678                12345678

5.在右/右邊填充字元 LPAD和RPAD

BYS@bys1>select lpad('abc',5,'*'),rpad('abc',5,'%') from dual;
LPAD( RPAD(
----- -----
**abc abc%%

6.刪除左/右邊的指定字元trim， LTRIM和RTRIM

從下面語句可以看出，只能刪除最左或最或邊同指定字元相同的字元，如有多個連續的同指定一樣的字元，可以連續刪除。
SQL> select ltrim('*a%%abc**a**','%'), ltrim('%%abc**a**','%'),rtrim('*a%%abc**a**','*'),rtrim('%%abc**a','*') from dual;
LTRIM('*A%%ABC**A**','%') LTRIM('%%ABC**A**','%') RTRIM('*A%%ABC**A**','*') RTRIM('%%ABC**A','*')
------------------------- ----------------------- ------------------------- ---------------------
*a%%abc**a**              abc**a**                *a%%abc**a                %%abc**a
不指定字元時，預設過濾的為空格符
從下面語句可以看出，trim是預設是同時濾過兩邊的字元。
SQL> select length(ltrim(' abc  ')),length(rtrim('  abc  ')),length(trim('  abc  ')) from dual;
LENGTH(LTRIM('ABC')) LENGTH(RTRIM('ABC')) LENGTH(TRIM('ABC'))
-------------------- -------------------- -------------------
                   5                    5                   3
SQL> select trim(leading '*' from '**abc%%'),trim(trailing '%' from '**abc%%'),trim(both '%' from '%%abc%%'),trim( '%'from '%%abc%%') from dual;
TRIM(LEADING'*'FROM'**ABC%%') TRIM(TRAILING'%'FROM'**ABC%%') TRIM(BOTH'%'FROM'%%ABC%%') TRIM('%'FROM'%%ABC%%')
----------------------------- ------------------------------ -------------------------- ----------------------
abc%%                         **abc                          abc                        abc                  

7.替換 REPLACE('string','s1','s2')；string希望被替換的字元或變數，s1被替換的字串，s2要替換的字串，

BYS@bys1>select replace('he love you','he','i') from dual;
REPLACE('H
----------
i love you

BYS@ bys3>select replace('s a',' ') from dual;    ---去空格
RE
--
sa

8.instr：求子串在父串中的位置，0表示沒有在父串中找到該子串。

如果一個字串中有多個與子串對應的字元，只顯示第一個查詢到的字元。
instr('abcdabc','a','2') 從字串的第2個字元開始搜尋符合a的字元。不指定從第幾個字元開始的話，預設從第1個字元開始搜尋。
SQL>select instr('abcdabc','a','2'),instr('baabc','a'),instr('bbc','a') from dual;
INSTR('ABCDABC','A','2') INSTR('BAABC','A') INSTR('BBC','A')
------------------------ ------------------ ----------------
                       5                  2                0