Oracle基礎教程：單行函數—字元類型函數

字元型
 ASCII
 CHR
 LOWER
 UPPER
 INITCAP
 CONCAT
 SUBSTR
 LENGTH
 INSTR
 TRIM
 dump
 lpad
 rpad
 REPLACE
 
ASCII('字元')
 返回字元的ASCII碼值
idle> select ASCII('a') from dual;
ASCII('A')
----------
 97

idle> select ASCII('A') from dual;

ASCII('A')
----------
 65

idle>

CHR('n')
 返回n的字元值 n是ASCII碼數
idle> select chr(65) from dual;

C
-
A

idle> select chr(39) from dual;

C
-
'

idle>

但是求單引號的ASCII碼寫法很特殊 兩個單引帶表一個單引

idle> select ASCII(''') from dual;
ERROR:
ORA-01756: quoted string not properly terminated

idle> select ASCII('''') from dual;

ASCII('''')
-----------
  39

idle>

LOWER(列名|運算式)
小寫轉換
idle> select lower('ABC') from dual;

LOW
---
abc

idle>
dual 為虛表,當一個語句不需要從表中擷取資料時,但又要維持SQL文法,Oracle才提供了虛表來解決這個問題

UPPER(列名|運算式)
大寫轉換

idle> select upper('abc') from dual;

UPP
---
ABC

idle>

INITCAP(列名|運算式)
每個詞的詞頭大寫 其他小寫

idle> select initcap('abC dEf XYZ') from dual;

INITCAP('AB
-----------
Abc Def Xyz

idle>

CONCAT(列名|運算式,列名|運算式)
將第一個字串和第二個字串串連

idle> select concat('abc','xyz') from dual;

CONCAT
------
abcxyz

不太常用,因為我們可以用 || 串連
idle> select 'abc'||'xyz' from dual;

'ABC'|
------
abcxyz

idle>

SUBSTR(列名|運算式,m,[n])
返回指定子串,該子串是從第m個字元開始,其長度為n,不指定n值則從m到最後

idle> select substr('abcdefxyz',4,3) from dual;

SUB
---
def

idle> select substr('abcdefxyz',4) from dual;

SUBSTR
------
defxyz

idle>

LENGTH(列名|運算式)
返回字串的長度
idle> select length('abcdefxyz') from dual;

LENGTH('ABCDEFXYZ')
-------------------
    9

idle>

INSTR (列名|運算式,'字串',[m],[n])
從運算式或列中搜尋給定的字串的所處位置,m代表從第幾個開始搜,n代表第幾次出現. m和n預設都是1

idle> select INSTR('abcdddxyz','d') from dual;

INSTR('ABCDDDXYZ','D')
----------------------
       4

idle>
idle> select INSTR('abcdddxyz','d',5) from dual;

INSTR('ABCDDDXYZ','D',5)
------------------------
         5

idle> select INSTR('abcdddxyz','d',5,2) from dual;

INSTR('ABCDDDXYZ','D',5,2)
--------------------------
    6

idle>

TRIM([leading|]trailing|both 要去掉的字元 FROM 源字串)
從源字串中去掉指定的字元 可以用leading tailing來修飾去掉的字串是在開頭或結尾,預設是兩者都  預設截取的是空格

idle> select trim('a' from 'aaabcdeaaafxyzaaa') from dual;

TRIM('A'FRO
-----------
bcdeaaafxyz

idle> select trim(leading 'a' from 'aaabcdeaaafxyzaaa') from dual;

TRIM(LEADING'A
--------------
bcdeaaafxyzaaa

idle> select trim(trailing 'a' from 'aaabcdeaaafxyzaaa') from dual;

TRIM(TRAILING'
--------------
aaabcdeaaafxyz

idle>

左補齊lpad 右補齊rpad
將不足20個字元的位置用指定符號填充.
idle> select lpad(ename,20,'-') ,rpad(ename,20,'-') from emp where ename like 'S%';

LPAD(ENAME,20,'-')   RPAD(ENAME,20,'-')
-------------------- --------------------
---------------SMITH SMITH---------------
---------------SCOTT SCOTT---------------
idle>

1 2 下一頁