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

# 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種類。這個種類具有最高方差，如果??? 比較最小利潤和最大利潤，那么他的利潤范圍很大。
??? 這就警告管理人員，一些書可能產生很小的利潤。而其他一些可以產生很大的利潤，可??? 以知道企業的經營數據。

轉載于:https://www.cnblogs.com/zhahost/archive/2008/12/02/1346171.html

總結

以上是生活随笔為你收集整理的oracle常用的字符和字符串处理类函数的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。