[数据库]Oracle字符串函数总结
0
2016-06-19 14:00:14
1.ASCII
功能:得到给定字符的字符编码
语法:ASCII(CHAR)
例句:
SQL>SELECTASCII('A')FROM DUAL;
ASCII('A')
----------
65
2.CHR
功能:得到给定数字对应的字符,与ASCII是一对反函数
语法:CHR(CHAR)
例句:
SQL>SELECTCHR(65)FROM DUAL;
CHR(65)
-------
A
3.UPPER
功能:将小写字符变为大写
语法:UPPER(STRING)
例句:
SQL>SELECTUPPER('b')FROM DUAL;
UPPER('B')
----------
B
SQL>SELECTUPPER('we are family')FROM DUAL;
UPPER('WEAREFAMILY')
--------------------
WE ARE FAMILY
4.LOWER
功能:将小写字母变为大写
语法:LOWER(STRING)
例句:
SQL>SELECTLOWER('D')FROM DUAL;
LOWER('D')
----------
d
SQL>SELECTLOWER('Long Time No See')FROM DUAL;
LOWER('LONGTIMENOSEE')
----------------------
longtimeno see
5.LENGTH
功能:显示字符串的字符长度
语法:LENGTH(STRING)
例句:
SQL>SELECTLENGTH('Long Time No See')FROM DUAL;
LENGTH('LONGTIMENOSEE')
-----------------------
16
SQL>SELECTLENGTH('好久不见')FROM DUAL;
LENGTH('好久不见')
------------------
4
6.LENGTHB
功能:显示字符串的字节长度
语法:LENGTHB(STRING)
例句:
SQL>SELECTLENGTH('Long Time No See')FROM DUAL;
LENGTHB('LONGTIMENOSEE')
-----------------------
16
SQL>SELECTLENGTH('好久不见')FROM DUAL;
LENGTHB('好久不见')
------------------
8
7.TRIM
功能:去掉字符串中最左边和最右边的空格
语法:TRIM(STRING)
例句:
SQL>SELECTTRIM(' Long Time No See ')FROM DUAL;
TRIM('LONGTIMENOSEE')
---------------------
LongTimeNo See
8.LTRIM
功能:去掉一个字符串中最左边开始在另一个字符串中存在的字符
语法:LTRIM(STRING1,STRING2) --STRING2缺省时为空格
例句:
SQL>SELECTLTRIM('UMKU Long Time No See UM','UMA')FROM DUAL;
LTRIM('UMKULONGTIMENOSEEUM','U
------------------------------
KU Long Time No See UM
SQL> SELECT LTRIM('LongTimeNo See UM') FROM DUAL;
LTRIM('LONGTIMENOSEEUM')
------------------------
Long Time No See UM
9.RTRIM
功能:去掉一个字符串中最右边开始在另一个字符串中存在的字符
语法:RTRIM(STRING1,STRING2) --STRING2缺省时为空格
例句:
SQL>SELECTRTRIM('UMKU Long Time No See UM','UMA')FROM DUAL;
RTRIM('UMKULONGTIMENOSEEUM','U
------------------------------
UMKU Long Time No See
SQL> SELECT RTRIM('LongTimeNo See ') FROM DUAL;
RTRIM('LONGTIMENOSEE')
----------------------
Long Time No See
10.REPLACE
语法:REPLACE(STRING,S1,S2)
功能:替换字符串中的部分字符。当S2缺省时删掉STRING中的所有S1
例句:
SQL>SELECTREPLACE('Your sister is so smart!','sister','brother')FROM DUAL;
REPLACE('YOURSISTERISSOSMART!'
------------------------------
Your brother is so smart!
SQL>SELECTREPLACE('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>selectinstr('abcdefdeghde','de',5,2) position from dual;
POSITION
----------
11
SQL>selectinstr('abcdefdeghde','de',5) position from dual;
POSITION
----------
7
SQL>selectinstr('abcdefdeghde','de') position from dual;
POSITION
----------
4
SQL>selectinstr('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>selectSUBSTR('welcome',1,3)from dual;
SUBSTR('WELCOME',1,3)
---------------------
wel
SQL>selectSUBSTR('welcome',0,3)from dual;
SUBSTR('WELCOME',0,3)
---------------------
wel
SQL>selectSUBSTR('welcome',-4,3)from dual;
SUBSTR('WELCOME',-4,3)
----------------------
com
SQL>selectSUBSTR('welcome',-4)from dual;
SUBSTR('WELCOME',-4)
--------------------
come
0
14.NVL
语法:NVL(String, VALUE)
功能:如果String是空值,返回VALUE,否则返回String
例句:
SQL>SELECTNVL(NULL,'2')FROM DUAL;
NVL(NULL,'2')
-------------
2
SQL>SELECTNVL('lk','2')FROM DUAL;
NVL('LK','2')
-------------
lk
15.NVL2
语法:NVL2(String, VALUE1, VALUE2)
功能:如果String不是空值,返回VALUE1, 否则返回VALUE2
例句:
SQL>SELECTNVL2('lk','2','8')FROM DUAL;
NVL2('LK','2','8')
------------------
2
SQL>SELECTNVL2(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>SELECTCONCAT('come','back')FROM DUAL;
CONCAT('COME','BACK')
---------------------
comeback
18.INITCAP
语法:INITCAP(String)
功能:将字符串中每个单词首字母大写
例句:
SQL>SELECTINITCAP('Long Time no see')FROM DUAL;
INITCAP('LONGTIMENOSEE')
------------------------
LongTimeNo See
19.RPAD
语法:RPAD(String,,n,s)
功能:在字符串中右端填充字符达到指定长度
例句:
SQL>SELECTRPAD('dfasfda',12,'ghg')FROM DUAL;
RPAD('DFASFDA',12,'GHG')
------------------------
dfasfdaghggh
20.LPAD
语法:RPAD(String,,n,s)
功能:在字符串中左端填充字符达到指定长度
例句:
SQL>SELECTLPAD('dfasfda',12,'ghg')FROM DUAL;
LPAD('DFASFDA',12,'GHG')
------------------------
ghgghdfasfda
21.SOUNDEX
语法:SOUNDEX(String)
功能:得到字符串的声音表示形式。
例句:
SQL>SELECTSOUNDEX('break')FROM DUAL;
SOUNDEX('BREAK')
----------------
B620
SQL>SELECTSOUNDEX('brake')FROM DUAL;
SOUNDEX('BRAKE')
----------------
B620
22.TRANSLATE
语法:TRANSLATE(String,s1,s2)
功能:回将所出现的s1中的每个字符替换为s2中的相应字符以后的string
例句:
SQL>SELECTTRANSLATE('Your sister is so smart!','sister','brother')FROM DUAL;
TRANSLATE('YOURSISTERISSOSMART
------------------------------
Youe brbthe rb bo bmaet!
23.NLSSORT
语法:NLSSORT(String,param)
功能:进行语言排序,且不影响当前会话.
例句:
SQL>SELECT NLSSORT('frank','NLS_SORT=BINARY_CI')FROM DUAL;
NLSSORT('FRANK','NLS_SORT=BINA
------------------------------
6672616E6B00
SQL> SELECT NLSSORT('fRank', 'NLS_SORT=BINARY_CI') FROM DUAL;
NLSSORT('FRANK','NLS_SORT=BINA
------------------------------
6672616E6B00
SELECT*FROM TEAM ORDERBY NLSSORT(排序字段,'NLS_SORT = SCHINESE_PINYIN_M');
转载请保留本文网址:http://www.shaoqun.com/a/232604.html
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们:admin@shaoqun.com。
oracle
0