ORACLE 字串操作

1 字串串連 SQL> select 'abc' || 'def' from dual;'ABC'|
------
abcdef2　小寫
SQL>select lower('ABC012');
lower
--------
abc012

3　大寫select upper('abc012');

upper
--------
ABC012

4　 左補全

 select lpad('abc', 5, '0');

lpad
-------
00abc

select lpad('abc', 5, '012');

lpad
-------
01abc 第3個參數為空白時，預設為space select lpad('abc', 5);

lpad
-------
   abc

select lpad('abc', 5, ' ');

lpad
-------
   abc 5 　右補全 select rpad('abc', 5, '0');

rpad
-------
abc00

select rpad('abc', 5, '012');

rpad
-------
abc01 6　左空白刪除select ltrim('          abc');

ltrim
-------
abc
7　右空白刪除 select ltrim('abc          ');

     ltrim
---------------
abc  8　左右空白刪除select trim('          abc          ');

btrim
-------
abc

9　字串替換 SQL> SELECT TRANSLATE('ababab' , 'a' , '1') FROM DUAL;TRANSL
------
1b1b1b 10　取子字串 SQL> select substr('abc012', 3, 2) from dual;SU
--
c0 11　字串長度 SQL> select length('abc012') from dual;LENGTH('ABC012')
----------------
               612　 數字轉化成字串 SQL> select to_char(123456, '999,999,999,999') from dual;TO_CHAR(123456,'
----------------
         123,456 13　字串轉化為數字 SQL> select to_number('123' || '456', '999999999999') from dual;TO_NUMBER('123'||'456','999999999999')
--------------------------------------
                                123456SQL> select to_number('123,456', '999,999,999,999') from dual;TO_NUMBER('123,456','999,999,999,999')
--------------------------------------
                                123456

#####################################
# 常用的字元和字串處理類函數
#####################################

# LOWER函數
作用：將字串轉換成為小寫字母
樣本：
    select firstname,lastname from customers
    where LOWER(lastname) = 'nelson';
# UPPER函數
作用：將字串轉換成為大寫字母
樣本：
    select firstname,lastname from customers
    where UPPER(lastname) = 'nelson';

# INITCAP函數
作用：將字串轉換成為混合大小寫，每一個單詞的開始都是一個大寫字母，
         其餘字元轉換成為小寫字母。
樣本：
    select firstname,lastname from customers
    where UPPER(lastname) = 'nelson';

    select initcap(firstname),initcap(lastname) from customers;  

# SUBSTR函數
作用：取子串函數
格式：SUBSTR(C,P,L)      其中：C表示字串，P表示要提取的起始的字串的位置，
           L表示提取的長度。如果P為負值，表示從字串的
           最後一位倒著向前取。
樣本：
    select distinct substr(zip,1,3)
    from customers;
  
    select distinct substr(zip,1,3),substr(zip,-3,2)
    from customers;

# LENGTH函數
作用：確定要分析的字串的長度
樣本：
    select destinct length(address)
    from customers;

# LPAD函數
作用：用於字串填充。填充的是字串左側的地區。
格式：LPAD(C,L,S)         其中：C表示將要填充的字串，L表示填充“之後”字串
            的長度，S表示用於填充的符號或者字元
樣本：
    select firstname,LPAD(firstname,12,' ')
    from customers
    where firstname like '%E%';

# RPAD函數
作用：用於字串填充。填充的是字串右側的地區。
格式：RPAD(C,L,S)         其中：C表示將要填充的字串，L表示填充“之後”字串
            的長度，S表示用於填充的符號或者字元
樣本：
    select firstname,RPAD(firstname,12,' ')
    from customers
    where firstname like '%E%';

# LTRIM函數

# RTRIM函數

# REPLACE函數
作用：用於字串的替換
格式：REPLACE(C,S,R) 其中：C表示將處理的字串，S表示想要尋找的字串，R表示
            將要替換的字串。類似於“尋找與替換”功能。
樣本：

# CONCAT函數
作用：字串串連函數    ||

 

 

  
####################################################################
# 數字函數
####################################################################

# ROUND函數
作用：用來將數字欄位舍入的指定的精度。
格式：ROUND(N,P)
樣本：
    select title,retail,ROUND(RETAIL,1),TRUNC(RETAIL,1)
    from books;

# TRUNC函數
作用：用來將數字欄位截斷的指定的精度。
格式：ROUND(N,P)
樣本：
    select title,retail,ROUND(RETAIL,1),TRUNC(RETAIL,1)
    from books;

 

####################################################################
# 日期函數
####################################################################

# Oracle的日期函數以DD－MON－YY格式顯示日期值，這種格式表示兩位的天數，三位的月份簡寫
    以及兩位的年份。
    例如：20－MAR－02
  
# MONTHS_BETWEEN函數
作用：表示兩個月之間相差的天數
樣本：
    select title,MONTHS_BETWEEN(orderdate,pubdate)
    from books NATURAL JOIN orders NATURAL JOIN orderitems
    where order# = 1009;

# ADD_MONTHS函數
     作用：表示在某個日期之後的時間
樣本：
    select title,pubdate,ADD_MONTHS(pubdate,60) "Drop Date"
    from books
    order by "Drop Date";

# NEXT_DAY函數
作用：
格式：    NEXT_DAY(d,DAY)    其中：d表示開始日期，DAY表示將要確定的一周中的某一天
樣本:
    select order#,orderdate,NEXT_DAY(orderdate,7)
    from orders
    where order# = 1010;

# TO_DATE函數
作用：
樣本：
    select order#,orderdate,shipdate
    from orders
    where orderdate = to_date('3-31-2003','MM-DD-YYYY');

##### 日期格式元素的值

    MONTH    全部寫出的月份名稱，添加空格，達到9個字元的總寬度 APRIL
    MON    月份名稱的三個字母簡寫       APR
    MM    月份的兩位元字值       04
    RM    羅馬數位月份        IV
    D               一周中某一天的數值
    DD     一月中某一天的數值
    DDD             一年中某一天的數值
    DAY             一周中某一天的名稱，添加空格，達到9個字元的寬度    Wednesday
    DY    一周中某一天的三個字母簡寫      WED
    YYYY    顯示4位的年份
    YYY、YY、Y      顯示年份的最後三位、最後兩位或者最後一位
    YEAR            全部寫出年份
    B.C. 或者 A.D.

####################################################################
# NVL函數
####################################################################
作用： 可以使用NVL函數來解決對可能包含NULL值的欄位執行數學運算時導致的問題。
    在Oracle9i中，NULL值不等於空格或者0。在計算中使用NULL值時，結果是NULL值。
    NVL函數使用一個值來代替現有的NULL值。

    select order#,orderdate,shipdate,shipdate-orderdate "Delay"
    from orders;
    在執行上述查詢時，有一些列是空白的。請注意！

    select order#,orderdate,NVL(shipdate,to_date('07-4-03','DD-MM-YY')),
    NVL(shipdate,to_date('07-4-03','DD-MM-YY'))-orderdate "Delay"
    from orders
    where order# = 1018;

    如果shipdate列的值為NULL，就用'07-04-03'替換shipdate的值。

 

####################################################################
# NVL2函數
####################################################################

作用： NVL2函數是NVL函數的一個變化形式，他允許不同的選項，這取決於是否存在NULL值。
格式： NVL2(x,y,z),其中y表示當x不是NULL時所替換的資料，z表示當x是NULL時所替換的數
    據。這使使用者在處理NULL值時更加靈活。

    例如：NVL2(commission,salary,salary+commission)
    表示：如果傭金是NULL，那麼工資總額就是工資；如果傭金不是NULL，那麼就將工資總額計算為工資加傭金。

    select order#,orderdate,NVL2(shipdate,'Shipped','NOT SHIPPED') "Status"
    from orders;

 

####################################################################
# TO_CHAR函數
####################################################################
作用： 用於將日期和數字轉換為格式化的字串。
格式：

    select title,to_char(pubdate,'MONTH    DD ,YYYY') "Publication Date",
    to_char(retail,'$9999.99') "Retail Price"
    from books
    where isbn = 0401140733;

####################################################################
# DECODE函數
####################################################################
作用：
格式：

       DECODE是Oracle公司獨家提供的功能，它是一個功能很強的函數。它雖然不是SQL的標準，
但對於效能非常有用。到目前，其他的資料庫供應商還不能提供類似DECODE的功能，甚至有的
資料庫的供應商批評Oracle的SQL不標準。實際上，這種批評有些片面或不夠水平。就象有些馬    車製造商抱怨亨利。福特的“馬車”不標準一樣。
在邏輯編程中，經常用到If – Then –Else 進行邏輯判斷。在DECODE的文法中，實際上就是這 樣的邏輯處理過程。它的文法如下：

DECODE(value, if1, then1,    if2,then2, if3,then3,    . . .    else )

Value 代表某個表的任何類型的任意列或一個通過計算所得的任何結果。當每個value值被測試    ，如果value的值為if1，Decode 函數的結果是then1；如果value等於if2，Decode函數結果是    then2；等等。事實上，可以給出多個if/then 配對。如果value結果不等於給出的任何配對時，    Decode 結果就返回else 。
需要注意的是，這裡的if、then及else 都可以是函數或計算運算式。

DECODE實現表的轉置

例子：希望將下面的列結果按照列的方式來顯示JOB內容：

SQL> select empno,ename,job,sal,deptno from emp
    2    order by deptno,job;

       EMPNO ENAME        JOB                SAL       DEPTNO
---------- ---------- --------- ---------- ----------
        7934 MILLER       CLERK             1300           10
        7782 CLARK        MANAGER           2450           10
        7839 KING         PRESIDENT         5000           10
        7788 SCOTT        ANALYST           3000           20
        7369 SMITH        CLERK              800           20
        7876 ADAMS        CLERK             1100           20
        7566 JONES        MANAGER           2975           20
        7938 趙元傑       軟體             12345           20
        7698 BLAKE        MANAGER           2850           30
        7499 ALLEN        SALESMAN          1600           30
        7654 MARTIN       SALESMAN          1250           30
        7844 TURNER       SALESMAN          1500           30
        7521 WARD         SALESMAN          1250           30

18 rows selected.

再看下面的查詢結果：

SQL> select deptno,job,sum(sal) from emp group by deptno,job;

      DEPTNO JOB           SUM(SAL)
---------- --------- ----------
          10 CLERK             1300
          10 MANAGER           2450
          10 PRESIDENT         5000
          20 ANALYST           3000
          20 CLERK             1900
          20 MANAGER           2975
          20 軟體             74070
          30 MANAGER           2850
          30 SALESMAN          5600

9 rows selected.

從上面的結果看，如果希望將JOB置換成列的方式，則只要用DECODE將JOB列進行描述即可。建立的視圖如下：

create or replace view empv as
select deptno,
sum( decode(job,'ANALYST', sal,0)) ANALYST,
sum( decode(job,'CLERK', sal,0)) CLERK,
sum( decode(job,'MANAGER', sal,0)) MANAGER,
sum( decode(job,'PRESIDENT', sal,0)) PRESIDENT,
sum( decode(job,'SALESMAN', sal,0)) SALESMAN,
sum( decode(job,'軟體', sal,0)) 軟體
from emp    group by deptno;

具體啟動並執行顯示樣本如下：
SQL> create or replace view empv as
    2    select deptno,
    3    sum( decode(job,'ANALYST', sal,0)) ANALYST,
    4    sum( decode(job,'CLERK', sal,0)) CLERK,
    5    sum( decode(job,'MANAGER', sal,0)) MANAGER,
    6    sum( decode(job,'PRESIDENT', sal,0)) PRESIDENT,
    7    sum( decode(job,'SALESMAN', sal,0)) SALESMAN,
    8    sum( decode(job,'軟體', sal,0)) 軟體
    9    from emp    group by deptno;

View created.

SQL> select * from empv;

      DEPTNO      ANALYST        CLERK      MANAGER    PRESIDENT     SALESMAN         軟體
---------- ---------- ---------- ---------- ---------- ---------- ----------
          10            0         1300         2450         5000            0            0
          20         3000         1900         2975            0            0        74070
          30            0            0         2850            0         5600            0

 

 

 

####################################################################
# SOUNDEX函數
####################################################################

####################################################################
# NESTING函數
####################################################################

####################################################################
# DUAL表
####################################################################

####################################################################
# SUM函數
####################################################################

select sum(retail-cost) "Total Profit"
from orderitems NATURAL JOIN books
where order# = 1007;

####################################################################
# AVG函數
####################################################################

select avg(retail-cost) "Average Profit"
from books
where category = 'COMPUTER';

select to_char(avg(retail-cost),'999.99') "Average Profit"
from books
where category = 'COMPUTER';

####################################################################
# COUNT函數
####################################################################

select count(distinct category) from books;

select distinct count(category) from books;

select count(*) from orders
where shipdate is null;

select count(shipdate) from orders
where shipdate is null;

          在COUNT函數中提供的參數是一個*時，存在的所有記錄都會計算在內，通過計算整個記錄， Count函數不會丟掉NULL值。

####################################################################
# MAX函數
####################################################################

####################################################################
# MIN函數
####################################################################

####################################################################
# GROUP BY子句
####################################################################

select category,to_char(avg(retail-cost),'999.99') Profit
from books
group by category

select customer#,order#,sum(quantity*retail) "Order Total"
from orders NATURAL JOIN orderitems NATURAL JOIN books
group by customer#,order#

####################################################################
# HAVING子句
####################################################################

select category,to_char(avg(retail-cost),'999.99') Profit
from books
group by category
having avg(retail-cost)>15;

select category,to_char(avg(retail-cost),'999.99') Profit
from books
where pubdate>to_date('01-01-02','DD-MM-YY')
group by category
having avg(retail-cost)>15;

按照訂單進行分組統計，但是只顯示總金額超過100美元的訂單。

select customer#,order#,sum(quantity*retail) "Order Total"
from orders NATURAL JOIN orderitems NATURAL JOIN books
group by customer#,order#
having sum(quantity*retail)>100;

 

####################################################################
# STDDEV函數
####################################################################
作用：
    計算指定欄位的標準差。“標準差”計算用來確定一組數字中的單個值與平均值的接近    程度。
樣本：
    select category,avg(retail-cost),stddev(retail-cost)
    from books
    group by category;

    要想解釋標準差計算的數值，必須將它與每一個種類的“平均利潤”作比較。例如：查     看上述結果中Cooking種類，圖書的平均利潤是8.60美元。但是大多數圖書都接近這個    平均值，還是大多數圖書都之產生1美元的利潤，而一本書產生了20美元的利潤？
    標準差就是有多少本書位於平均值附近的某個範圍之內的統計近似值。

####################################################################
# VARIANCE函數
####################################################################

作用： 用來確定資料在一個組中的分散程度。一組記錄的方差是根據指定欄位的最大值
    和最小值計算的。
    如果資料值很密集，那麼方差就很小；但是如果資料包含極端的值（很大或者很小）
    那麼方差就很大。
樣本：
    select category,variance(retail-cost),min(retail-cost),max(retail-cost)
    from books
    group by category;

    對結果的解釋：
    上述查詢結果列出了BOOKS表中所有圖書的種類、每一個種類的利潤方差以及每個種類
    中的最小利潤和最大利潤（為了進行比較）。與標準差一樣，如果一組資料只包含一個      值，那麼計算得到的方差是0。但是與標準差不同，方差不是用與來源資料相同的單位計    算的。

    要想解釋VARRANCE函數的結果，必須查看這個值有多大或者多小。例如：Cooking種類    具有比其他種類更小的方差。這意味著Cooking種類中的圖書利潤更密集（也就是說，    利潤沒有覆蓋更大的範圍）。
                  看一下Cooking種類中的圖書的最小利潤和最大利潤，注意，利潤範圍是2.30美元，
    （9.75.7.45）。另一方面，我們看看Family Life種類。這個種類具有最高方差，如果    比較最小利潤和最大利潤，那麼他的利潤範圍很大。
    這就警告管理員，一些書可能產生很小的利潤。而其他一些可以產生很大的利潤，可    以知道企業的經營資料。