oracle中的單行函式（一）

單行函式：每處理一條記錄返回一條結果.

Single-row functions return one row of results for each record processed.

下面介紹下oracle中的大小寫轉換函式，字串操作函式。

大小寫轉換函式：lower,upper,initcap

字串操作函式：substr,instr,length,lpad/rpad,ltrim/rtrim,replace,translate,concat

lower語法格式：

lower（c）.c是字串或者是要轉換的域。

SQL> select first_name,last_name,commission_pct

2 from employees

3 where lower(last_name)='king';

FIRST_NAME LAST_NAME COMMISSION_PCT

-------------------- ------------------------- --------------

Steven King

Janette King 0.35

lower函式在select語句中使用不會改變資料庫中字元的大小寫形式。當在插入或者

更新語句中使用lower函式的時候，可能會影響資料庫中儲存的字元的大小寫形式。

注意查詢展現的字元的大小寫形式還是儲存的時候的大小寫形式。

如果想讓展現的字元都變成大寫或者小寫可以使用下面的方式：

SQL> select lower(first_name),lower(last_name),commission_pct

2 from employees

3 where lower(last_name)='king';

LOWER(FIRST_NAME) LOWER(LAST_NAME) COMMISSION_PCT

-------------------- ------------------------- --------------

steven king

janette king 0.35

注意上面的表頭是帶有函式的，可以使用別名來消除表頭。

SQL> select lower(first_name) as first_name,lower(last_name) as last_name,commission_pct

2 from employees

3 where lower(last_name)='king';

FIRST_NAME LAST_NAME COMMISSION_PCT

-------------------- ------------------------- --------------

steven king

janette king 0.35

upper函式的語法格式：

upper(c).c表示要轉換的字元或者需要轉換的域。

SQL> select firstname,lastname,city

2 from customers

3 where lastname=upper(&lastname);

輸入 lastname 的值: 'smith'

原值 3: where lastname=upper(&lastname)

新值 3: where lastname=upper('smith')

FIRSTNAME LASTNAME CITY

-------------------- -------------------- ------------------------

LEILA SMITH TALLAHASSEE

JENNIFER SMITH MORRISTOWN

上面的SQL語句先將lastname變數的值轉換成大寫，然後再與資料庫中儲存的值進行比較。

使用上面的方法，使用者輸入的時候不必考慮資料庫中儲存值的大小寫形式，另外還可以

提高oracle伺服器的效率，因為轉換的只是輸入條件，而不是整張表中的某列值。

initcap函式的語法格式：

initcap (c).c表示需要被轉換的字串，或者需要被轉換的域。initcap函式會將要

轉換的內容的每個單詞的首字母轉換成大寫的形式，其他的是小寫格式。這樣方便閱讀。

SQL> select first_name,last_name,commission_pct

2 from employees

3 where last_name=initcap(&lastname);

輸入 lastname 的值: 'king'

原值 3: where last_name=initcap(&lastname)

新值 3: where last_name=initcap('king')

FIRST_NAME LAST_NAME COMMISSION_PCT

---------------------------------------- -------------------------------------------------- --------------

Steven King

Janette King .35

注意employees表中的first_name與last_name列的第一個字母是大寫的。

substr函式的語法格式：

substr(c, p,l).c代表字串或者需要處理的域，p代表開始字串抽取的位置，l表示抽取的字串長度。

customers表中的zip列的前三個字元有特殊的意義，表示顧客來自那個州。

現在把這些字串抽取出來。以確定顧客來自那些州。

SQL> select distinct substr(zip,1,3)

2 from customers;

SUBSTR(ZIP,1,3)

------------------------

331

079

323

837

122

312

904

820

915

490

787

substr(zip,1,3),表示對zip列進行抽取，從第一個字元開始，抽取的長度是3.

SQL> select zip,substr(zip,1,3),substr(zip,-3,2)

2 from customers;

ZIP SUBSTR(ZIP,1,3) SUBSTR(ZIP,-3,2)

---------- ------------------------ ----------------

32328 323 32

90404 904 40

32306 323 30

83707 837 70

98115 981 11

12211 122 21

78710 787 71

82003 820 00

91510 915 51

30314 303 31

60606 606 60

字串開始抽取的位置也可以是負數，表示從字串反向開始計數。

substr(zip,-3,2)，表示從字串倒數第三個字元開始抽取，長度是2.

instr函式的語法格式

強制的引數有：用於查詢的字串，待查詢的子字串（用單引號括起來）。

可選的引數有：用於字串中開始查詢的位置，用於表示匹配出現的次數。

如果查詢到，則返回匹配開始處的第一個字元的位置，如果沒有查詢到則返回0.

SQL> select phone_number,instr(phone_number,'.') "第1個點的位置"，

2 instr(phone_number,'.',5) "從第5個字元開始"，

3 instr(phone_number,'.',1,2) "第2個點出現的位置"

4 from employees;

PHONE_NUMBER 第1個點的位置從第5個字元開始第2個點出現的位置

---------------------------------------- ------------- --------------- -----------------

515.123.4567 4 8 8

515.123.4568 4 8 8

515.123.4569 4 8 8

590.423.4567 4 8 8

590.423.4568 4 8 8

590.423.4569 4 8 8

590.423.4560 4 8 8

590.423.5567 4 8 8

515.124.4569 4 8 8

515.124.4169 4 8 8

515.124.4269 4 8 8

length函式的語法

length(c).c表示要求其長度的字串或者域。

求customers表中位址列的長度。

SQL> select length(address) "Length"

2 from customers;

Length

----------

12

13

11

18

12

16

12

13

17

11

length函式常用於確定表中的某一列是否，考慮應該增加長度了。

lpad函式的語法格式

lpad(c, l, s) c表示要處理的字串，l表示填充以後字串的長度（包括原始的數

據與填充符），s代表用於填充的特殊字元。lpad函式將在列的左邊填充特定的字元，而不是空格。

SQL> select lastname,lpad(lastname,12,'#')

2 from customers;

LASTNAME LPAD(LASTNAME,12,'#')

---------- ------------------------------------------------

MORALES #####MORALES

THOMPSON ####THOMPSON

SMITH #######SMITH

PIERSON #####PIERSON

GIRARD ######GIRARD

CRUZ ########CRUZ

GIANA #######GIANA

JONES #######JONES

PEREZ #######PEREZ

要填充的字串要用單引號括起來。

相應的有rpad與lpad在語法上是相同的，只不過實在要出來的字串或者域的右邊添

加特殊的字元。

replace函式的語法格式

replace(c, s, r).c代表要原來的字串或者域，s表示要查詢的字串，r表示用來

代替s的字串。replace函式會用r多次替換s，如果可以查詢到多個s的話。

SQL> select address,replace(address,'P.O.','POST OFFICE')

2 from customers

3 where state='FL';

ADDRESS REPLACE(ADDRESS,'P.O.','POSTOF

-------------------- ------------------------------------

P.O. BOX 651 POST OFFICE BOX 651

P.O. BOX 66 POST OFFICE BOX 66

357 WHITE EAGLE AVE. 357 WHITE EAGLE AVE.

P.O. BOX 677 POST OFFICE BOX 677

要查詢與要替換的字串要用單引號括起來.

translate的使用

可以使用translate函式來查詢並替換字串中的單個字元，translate函式會替換多

次（如果存在需要替換的字元的話）直到字串的末尾。

SQL> select translate('I love oracle database.','o','O')

2 from dual;

TRANSLATE('ILOVEORACLEDATABASE.','O','O')

----------------------------------------------

I lOve Oracle database.

字串中所有出現的小寫的o都會替換成大寫的O.

SQL> select translate('I love oracle database.','Io','iO')

2 from dual;

TRANSLATE('ILOVEORACLEDATABASE.','IO','IO')

----------------------------------------------

i lOve Oracle database.

字串中所有出現的小的o都會被替換成大小的O,大寫的I都會被替換成小寫的i。

即：o.....>O,I....>i

translate是字元級別的替換，replace是字串級別的替換。比如在

translate('hello everybody.','body','oney')中b 都會替換成o字元，o字元都會

替換成n字元，d字元都會替換成e字元，y字元替換成字元y。

concat函式的語法格式

concat(c1, c2),c1表示第一個需要連線的字串或者列，c2表示第二個需要連線的

字串或者列。

SQL> select concat('I love ','oracle database.')

2 from dual;

CONCAT('ILOVE','ORACLEDATABASE.')

----------------------------------------------

I love oracle database.

ltrim函式的語法格式

ltrim(c, s).c 表示要處理的域，s表示左邊需要移除的字串。如果要處理的域中

沒有發現要裁剪的字串s，則不裁剪。rtrim的用法參照ltrim。

SQL> select ltrim('oracle nosql database','oracle')

2 from dual;

LTRIM('ORACLENOSQLDATABASE','O

------------------------------

nosql database

oracle中的單行函式（一）

相關文章