Oracle 內建函式三

Ø REGEXP_REPLACE

該函式是Oracle10g新增的函式，擴充套件REPLACE功能，該函式用於按照特定表示式的規則進行替換字串，即：支援正規表示式。

格式：

REGEXP_REPLACE(SOURCE_STRING,PATTEN[,REPLACE_STRING[,POSITION[,OCCURRENCE[,MATCH_PARAMETER]]]])

SQL> SELECT REGEXP_REPLACE('TANG2010YUN||-||tang2012yun','[a-z]','') FROM DUAL;

REGEXP_REPLACE('TANG2010YUN||-

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

TANG2010YUN||-||2012

SQL> SELECT REGEXP_REPLACE('TANG2010YUN||-||tang2012yun','d','') FROM DUAL;

REGEXP_REPLACE('TANG2010YUN||-

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

TANGYUN||-||tangyun

SQL> SELECT REGEXP_REPLACE('TANG2010YUN||-||tang2012yun','w','') FROM DUAL;

REGEXP_REPLACE('TANG2010YUN||-

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

||-||

Ø REGEXP_SUBSTR

該函式是Oracle10g新增的函式，擴充套件REPLACE功能，該函式用於按照特定表示式的規則進行替換字串，即：支援正規表示式。

格式：

REGEXP_SUBSTR(SOURCE_STRING,PATTEN[,REPLACE_STRING[,POSITION[,OCCURRENCE[,MATCH_PARAMETER]]]])

SQL> SELECT REGEXP_SUBSTR('TANG2010YUN||-||tang2012yun','[a-z].*') FROM DUAL;

REGEXP_SUBSTR('TANG2010YUN||-|

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

tang2012yun

SQL> SELECT REGEXP_SUBSTR('TANG2010YUN||-||tang2012yun','[a-z]') FROM DUAL;

REGEXP_SUBSTR('TANG2010YUN||-|

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

T

SQL> SELECT REGEXP_SUBSTR('TANG2010YUN||-||tang2012yun','d{2,}') FROM DUAL;

REGEXP_SUBSTR('TANG2010YUN||-|

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

2010

Ø REPLACE

REPLACE函式在一字串中搜尋一指定的子串，並將其替換成另外一指定串。

REPLACE(string,substring[,replace_string])

SQL> SELECT REPLACE('TANGYUN121','12','') FROM DUAL;

REPLACE('TANGYUN121','12','')

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

TANGYUN1

Ø ROUND

ROUND函式將一日期處理（舍入）成第二個引數指定的形式。

ROUND函式將一數值處理（舍入）成第二個引數指定的形式的十進位制數。

SQL> SELECT ROUND(24.9,-1),ROUND(24.4),ROUND(24.45,1) FROM DUAL;

ROUND(24.9,-1) ROUND(24.4) ROUND(24.45,1)

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

20 24 24.5

Ø ☆ROWIDTOCHAR

ROWIDTOCHAR函式將一行標誌碼（ROWID）轉換成一字串。

其具體的語法格式如下：

ROWIDTOCHAR（rowid）

其中：rowid 一ROWID型資料

下面是該函式的使用情況：

SQL>SELECT ROWIDTOCHAR(ROWID) FROM dual;

ROWIDTOCHAR(ROWID)

----------

AAAADCAABAAAAVUAAA

Ø RPAD

RPAD函式在字串的後面追加若干字元。

LPAD函式從右面填充指定的字串。

SQL> SELECT RPAD('TANGYUN',12,'*') FROM DUAL;

RPAD('TANGYUN',12,'*')

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

TANGYUN*****

Ø SCN_TO_TIMESTAMP

SCN_TO_TIMESTAMP(number)用於根據輸入的SCN返回所對應的大概日期時間。其中number用於指定SCN值。

Ø RTRIM

RTRIM函式刪除字串右邊的字首字元。一般字首字元總是空格。

右邊單個字元單個字元的去除。這裡是用單個字元去匹配，並不是用兩個字元去匹配，但是必須是在最右邊

SQL> SELECT LTRIM('****TANGYUN') "TRIM",LTRIM('***!*!***TANGYUN','*!') "TRIM" FROM DUAL;

SQL> SELECT RTRIM('TANGYUN****') "RTRIM",RTRIM('TANGYUN****','*') "RTRIM",RTRIM('TANGYUN***!*!***','*!') "RTRIM" FROM DUAL;

RTRIM RTRIM RTRIM

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

TANGYUN**** TANGYUN TANGYUN

Ø SIGN

SIGN函式返回實參的符號。對於負數返回-1，0返回0，正數返回+1。

SQL> SELECT SIGN(-100),SIGN(0),SIGN(100) FROM DUAL;

SIGN(-100) SIGN(0) SIGN(100)

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

-1 0 1

Ø SIN

SIN函式返回一個角度的正弦值。

其具體的語法格式如下：

SIN（angle）

其中：

angle待求解其正弦的角度。是一角度表示式。可參考函式COS瞭解從弧度到角度的轉換

SQL> SELECT SIN(90*3.1415926/180),SIN(0) FROM DUAL;

SIN(90*3.1415926/180) SIN(0)

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

1 0

Ø ☆SOUNDEX

SOUNDEX函式根據以下規則返回一個字串的發音描述。

1、保留字串的首字母

2、刪除下面的所有字母：a、e、h、I、o、u、w和y。

3、首字母后面的任一字母均按如下規則匹配於某一數字：

b,f,p,v=1

c,g,j,k,q,s,x,z=2

d,t=3

I=4

m,n=5

r=6

4、結果被縮寫並轉換成一個4位的數字

其具體的語法格式如下：

SOUNDEX（string）

其中：

string 任意字串

下面是該函式的使用情況：

SQL> SELECT SOUNDEX('Gennick'),SOUNDEX('Genick'),SOUNDEX('Genyk') FROM DUAL;

SOUNDEX('GENNICK') SOUNDEX('GENICK') SOUNDEX('GENYK')

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

G520 G520 G520

Ø SQRT

SQRT(n)函式返回一數字的算術平方根。n必須是一個大於等於0的數字。

SQL> SELECT SQRT(100),SQRT(3) FROM DUAL;

SQRT(100) SQRT(3)

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

10 1.73205080

Ø SUBSTR/ SUBSTRB

SUBSTR返回一字串得指定字串。

SQL> SELECT SUBSTR('TANGYUNyuntang',-4),SUBSTR('TANGYUNyuntang',5) FROM DUAL;

SUBSTR('TANGYUNYUNTANG',-4) SUBSTR('TANGYUNYUNTANG',5)

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

tang YUNyuntang

Ø TAN

TAN函式返回一個角度的正切值。

其具體的語法格式如下：

TAN（angle）

其中：angle用弧度表示。可參考函式COS瞭解從弧度到角度的轉換

SQL> SELECT TAN(225*3.1415926535/180),TAN(0),TAN(1) FROM DUAL;

TAN(225*3.1415926535/180) TAN(0) TAN(1)

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

0.999999999775517 0 1.55740772

Ø TANH

TANH函式返回一個角度的十六進位制正切值。TANH（angle）

SQL> SELECT TANH(225*3.1415926535/180),TANH(0) FROM DUAL;

TANH(225*3.1415926535/180) TANH(0)

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

0.999223894878467 0

Ø TIMESTAMP_TO_SCN

TIMESTAMP_TO_SCN(timestamp)用於根據輸入的TIMESTAMP值返回對應的SCN值。

SQL> SELECT timestamp_to_scn(SYSDATE-1) FROM DUAL;

TIMESTAMP_TO_SCN(SYSDATE-1)

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

2689640

Ø TO_CHAR

TO_CHAR函式將一個日期型資料轉換成一個字元型資料。

TO_CHAR函式將一個數值型資料轉換成一個字元型資料。

SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD'),SYSDATE FROM DUAL;

TO_CHAR(SYSDATE,'YYYY-MM-DD') SYSDATE

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

2010-10-31 2010-10-31

Ø TO_DATE

TO_DATE函式將一個字元型資料轉換成一個日期型資料。

SQL> SELECT TO_DATE('2010-10-31','YYYY-MM-DD'),SYSDATE FROM DUAL;

TO_DATE('2010-10-31','YYYY-MM- SYSDATE

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

2010-10-31 2010-10-31

Ø TO_LOB

TO_LOB函式將一個LONG或LONG RAW型資料轉換成下列型別：CLOB、BLOB或NCLOB。當僅僅需要在INSERT語句中用LONG型資料代替LOB型資料時，可以使用該函式。

其具體的語法格式如下：

TO_LOB（long_value）

其中：

long_value 待轉換為CLOB或NCLOB等型別資料的一個 LONG或LONG RAW型資料。LONG RAW型資料轉 換成BLOB型資料

下面是該函式的使用情況：

INSERT INTO new_table(clob_value) SELECT TO_LOB(long_value) FROM old_table;

Ø TO_MULTI_BYTE

該函式用於將單位元組的字串變為多位元組的字串。與TO_SINGLE_BYTE對應。

SQL> SELECT TO_MULTI_BYTE('TANGYUN2010'),'TANGYUN2010' FROM DUAL;

TO_MULTI_BYTE('TANGYUN2010') 'TANGYUN2010'

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

ＴＡＮＧＹＵＮ２０１０ TANGYUN2010

Ø TO_NUMBER

TO_NUMBER函式將一字串轉換成一數字。

SQL> SELECT TO_NUMBER('20.10'),TO_NUMBER('20') FROM DUAL;

TO_NUMBER('20.10') TO_NUMBER('20')

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

20.1 20

SQL> SELECT TO_NUMBER('$123,456.78','$999,999.99') FROM DUAL;

TO_NUMBER('$123,456.78','$999,

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

123456.78

Ø TO_SINGLE_BYTE

把中文全形字元轉換為英文半形。

SQL> SELECT TO_SINGLE_BYTE('，）') FROM DUAL;

TO_SINGLE_BYTE('，）')

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

,)

Ø TRANSLATE

替換對應的字元。

SQL> SELECT TRANSLATE('SMITH 20','0123456789','##########') FROM DUAL;

TRANSLATE('SMITH20','012345678

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

SMITH ##

SQL> SELECT REPLACE(TRANSLATE('SMITH 20','0123456789','##########'),'#','') FROM DUAL;

REPLACE(TRANSLATE('SMITH20','0

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

SMITH

Ø TRIM

TRIM函式將字串的字首（或尾隨）字元刪除。

TRIM([LEADING|TRAILING|BOTH][trimchar FROM] string)

LEADING 指明僅僅將字串的字首字元刪除

TRAILING 指明僅僅將字串的尾隨字元刪除

BOTH 指明既刪除字首字元，也刪除尾隨字元。這也是預設方式

SQL> SELECT TRIM(' Ashley ') FROM DUAL;

TRIM('ASHLEY')

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

Ashley

SQL> SELECT TRIM(LEADING '*' FROM'***Ashley***') FROM DUAL;

TRIM(LEADING'*'FROM'***ASHLEY*

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

Ashley***

Ø TRUNC

TRUNC函式為指定元素而截去的日期值。

TRUNC函式返回處理後的數值，其工作機制與ROUND函式極為類似，只是該函式不對指定小數前或後的部分做相應舍入選擇處理，而統統截去。

Ø UNISTR

用於返回輸入字串的UNICODE字元。

SQL> SELECT UNISTR('0D6') FROM DUAL;

UNISTR('0D6')

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

?

Ø UID

UID函式返回一個整數值。對於當前資料庫使用者來它是唯一的。

SQL> SELECT UID FROM DUAL;

UID

----------

56

該數值來源於V$SESSION中的USER#列。

Ø UPPER

UPPER函式間返回字串的大寫形式。

SQL> SELECT UPPER('TANgyuN') FROM DUAL;

UPPER('TANGYUN')

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

TANGYUN

Ø CARDINALITY(nest_table)

用於返回巢狀表的實際元素個數。

Ø COLLECT(column)

用於根據輸入的列和被選擇的行建立巢狀表結果。

Ø POWERMULTISET(EXPR)

用於生成巢狀表的超集（包括所有非空的巢狀表）。

Ø SET(nest_table)

該函式用於取消巢狀表的重複結果，並生成新的巢狀表。

[@more@]