1.ASCII 功能:得到給定字元的字元編碼 語法:ASCII(CHAR) 例句: SQL> SELECT ASCII('A') FROM DUAL; ASCII('A') ---------- 65 2.CHR 功能:得到給定數字對應的字元,與ASCII是一對反函數 語法... ...
1.ASCII
功能:得到給定字元的字元編碼
語法:ASCII(CHAR)
例句:
SQL> SELECT ASCII('A') FROM DUAL;
ASCII('A')
----------
65
2.CHR
功能:得到給定數字對應的字元,與ASCII是一對反函數
語法:CHR(CHAR)
例句:
SQL> SELECT CHR(65) FROM DUAL;
CHR(65)
-------
A
3.UPPER
功能:將小寫字元變為大寫
語法:UPPER(STRING)
例句:
SQL> SELECT UPPER('b') FROM DUAL;
UPPER('B')
----------
B
SQL> SELECT UPPER('we are family') FROM DUAL;
UPPER('WEAREFAMILY')
--------------------
WE ARE FAMILY
4.LOWER
功能:將小寫字母變為大寫
語法:LOWER(STRING)
例句:
SQL> SELECT LOWER('D') FROM DUAL;
LOWER('D')
----------
d
SQL> SELECT LOWER('Long Time No See') FROM DUAL;
LOWER('LONGTIMENOSEE')
----------------------
long time no see
5.LENGTH
功能:顯示字元串的字元長度
語法:LENGTH(STRING)
例句:
SQL> SELECT LENGTH('Long Time No See') FROM DUAL;
LENGTH('LONGTIMENOSEE')
-----------------------
16
SQL> SELECT LENGTH('好久不見') FROM DUAL;
LENGTH('好久不見')
------------------
4
6.LENGTHB
功能:顯示字元串的位元組長度
語法:LENGTHB(STRING)
例句:
SQL> SELECT LENGTH('Long Time No See') FROM DUAL;
LENGTHB('LONGTIMENOSEE')
-----------------------
16
SQL> SELECT LENGTH('好久不見') FROM DUAL;
LENGTHB('好久不見')
------------------
8
7.TRIM
功能:去掉字元串中最左邊和最右邊的空格
語法:TRIM(STRING)
例句:
SQL> SELECT TRIM(' Long Time No See ') FROM DUAL;
TRIM('LONGTIMENOSEE')
---------------------
Long Time No See
8.LTRIM
功能:去掉一個字元串中最左邊開始在另一個字元串中存在的字元
語法:LTRIM(STRING1,STRING2) --STRING2預設時為空格
例句:
SQL> SELECT LTRIM('UMKU Long Time No See UM','UMA') FROM DUAL;
LTRIM('UMKULONGTIMENOSEEUM','U
------------------------------
KU Long Time No See UM
SQL> SELECT LTRIM(' Long Time No See UM') FROM DUAL;
LTRIM('LONGTIMENOSEEUM')
------------------------
Long Time No See UM
9.RTRIM
功能:去掉一個字元串中最右邊開始在另一個字元串中存在的字元
語法:RTRIM(STRING1,STRING2) --STRING2預設時為空格
例句:
SQL> SELECT RTRIM('UMKU Long Time No See UM','UMA') FROM DUAL;
RTRIM('UMKULONGTIMENOSEEUM','U
------------------------------
UMKU Long Time No See
SQL> SELECT RTRIM(' Long Time No See ') FROM DUAL;
RTRIM('LONGTIMENOSEE')
----------------------
Long Time No See
10.REPLACE
語法:REPLACE(STRING,S1,S2)
功能:替換字元串中的部分字元。當S2預設時刪掉STRING中的所有S1
例句:
SQL> SELECT REPLACE('Your sister is so smart!','sister','brother') FROM DUAL;
REPLACE('YOURSISTERISSOSMART!'
------------------------------
Your brother is so smart!
SQL> SELECT REPLACE('Your sister is so smart!','sister') FROM DUAL;
REPLACE('YOURSISTERISSOSMART!'
------------------------------
Your is so smart!
11.INSTR
語法:INSTR(string,s,n1,n2)
功能:判斷一個字元串是否含有另外的字元串,並返回所在的位置。且可以指定開始 遍歷的位置,和出現的第次,沒有則返回零。n1和n2的預設值都為1。
例句:
SQL> select instr('abcdefdeghde','de',5,2) position from dual;
POSITION
----------
11
SQL> select instr('abcdefdeghde','de',5) position from dual;
POSITION
----------
7
SQL> select instr('abcdefdeghde','de') position from dual;
POSITION
----------
4
SQL> select instr('abcdefdeghde','fe',5,2) position from dual;
POSITION
----------
0
12.INSTRB
語法:INSTRB(string,s,n1,n2)
功能:和INSTR相同,只是操作的對參數字元使用的位置的是位元組.
例句:
......
13.SUBSTR
語法:SUBSTR(string,start,count)
功能:截取字元串的某一段實符,start為開始截取的位置,count為截取的字元個數。start為0和1時是等效的,start為負時表示應該從右往左數,count預設時返回字元表達式的值結束前的全部字元。
例句:
SQL> select SUBSTR('welcome' ,1,3) from dual;
SUBSTR('WELCOME',1,3)
---------------------
wel
SQL> select SUBSTR('welcome' ,0,3) from dual;
SUBSTR('WELCOME',0,3)
---------------------
wel
SQL> select SUBSTR('welcome' ,-4,3) from dual;
SUBSTR('WELCOME',-4,3)
----------------------
com
SQL> select SUBSTR('welcome' ,-4) from dual;
SUBSTR('WELCOME',-4)
--------------------
come
0
14.NVL
語法:NVL(String, VALUE)
功能:如果String是空值,返回VALUE,否則返回String
例句:
SQL> SELECT NVL(NULL, '2') FROM DUAL;
NVL(NULL,'2')
-------------
2
SQL> SELECT NVL('lk', '2') FROM DUAL;
NVL('LK','2')
-------------
lk
15.NVL2
語法:NVL2(String, VALUE1, VALUE2)
功能:如果String不是空值,返回VALUE1, 否則返回VALUE2
例句:
SQL> SELECT NVL2('lk', '2','8') FROM DUAL;
NVL2('LK','2','8')
------------------
2
SQL> SELECT NVL2(NULL, '2','8') FROM DUAL;
NVL2(NULL,'2','8')
------------------
8
16.NANVI
語法:NANVI(String, VALUE)
功能:如果String不是數字,那麼返回VALUE,否則返回String
例句:
SQL> SELECT NANVI('3DF', '34') FROM DUAL; --Oracle 11g才有
NANVI('3DF', '34')
------------------
34
17.CONCAT
語法:CONCAT(String1,String2)
功能:連接兩個字元串
例句:
SQL> SELECT CONCAT('come','back') FROM DUAL;
CONCAT('COME','BACK')
---------------------
comeback
18.INITCAP
語法:INITCAP(String)
功能:將字元串中每個單詞首字母大寫
例句:
SQL> SELECT INITCAP('Long Time no see') FROM DUAL;
INITCAP('LONGTIMENOSEE')
------------------------
Long Time No See
19.RPAD
語法:RPAD(String,,n,s)
功能:在字元串中右端填充字元達到指定長度
例句:
SQL> SELECT RPAD('dfasfda',12,'ghg') FROM DUAL;
RPAD('DFASFDA',12,'GHG')
------------------------
dfasfdaghggh
20.LPAD
語法:RPAD(String,,n,s)