1、LOWER(c) 将指定字符串内字符变为小写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型 例如:SELECT LOWER('WhaT is tHis') FROM DUAL;
2、UPPER(c) 将指定字符串内字符变为大写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型 例如:SELECT UPPER('WhaT is tHis') FROM DUAL;
3、LPAD(c1,n[,c2]) 返回指定长度=n的字符串,需要注意的有几点: 如果nc1.length则从右到左截取指定长度返回; 如果n>c1.length and c2 is null,以空格从左向右补充字符长度至n并返回; 如果n>c1.length and c2 is not null,以指定字符c2从左向右补充c1长度至n并返回; 例如:SELECT LPAD('WhaT is tHis',5),LPAD('WhaT is tHis',25),LPAD('WhaT is tHis',25,'-') FROM DUAL; 最后大家再猜一猜,如果n0,结果会怎么样
4、RPAD(c1,n[,c2]) 返回指定长度=n的字符串,基本与上同,不过补充字符是从右向左方向正好与上相反; 例如:SELECT RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') FROM DUAL;
5、TRIM([[LEADING||TRAILING||BOTH] c2 FROM] c1) 哈哈,被俺无敌的形容方式搞晕头了吧,这个地方还是看图更明了一些。 看起来很复杂,理解起来很简单: 如果没有指定任何参数则oracle去除c1头尾空格 例如:SELECT TRIM(' WhaT is tHis ') FROM DUAL; 如果指定了c2参数,则oracle去掉c1头尾c2(这个建议细致测试,有多种不同情形的哟) 例如:SELECT TRIM('W' FROM 'WhaT is tHis w W') FROM DUAL; 如果指定了leading参数则会去掉c1头部c2 例如:SELECT TRIM(leading 'W' FROM 'WhaT is tHis w W') FROM DUAL; 如果指定了trailing参数则会去掉c1尾部c2 例如:SELECT TRIM(trailing 'W' FROM 'WhaT is tHis w W') FROM DUAL; 如果指定了both参数则会去掉c1头尾c2(跟不指定有区别吗?没区别!) 例如:SELECT TRIM(both 'W' FROM 'WhaT is tHis w W') FROM DUAL;
注意:c2长度=1
6、LTRIM(c1[,c2]) 千万表以为与上面那个长的像,功能也与上面的类似,本函数是从字符串c1左侧截取掉与指定字符串c2相同的字符并返回。如果c2为空则默认截取空格。 例如:SELECT LTRIM('WWhhhhhaT is tHis w W','Wh') FROM DUAL;
7、RTRIM(c1,c2)与上同,不过方向相反 例如:SELECT RTRIM('WWhhhhhaT is tHis w W','W w') FROM DUAL;
8、REPLACE(c1,c2[,c3]) 将c1字符串中的c2替换为c3,如果c3为空,则从c1中删除所有c2。 例如:SELECT REPLACE('WWhhhhhaT is tHis w W','W','-') FROM DUAL;
10、SUBSTR(c1,n1[,n2]) 截取指定长度的字符串。稍不注意就可能充满了陷阱的函数。 n1=开始长度; n2=截取的字符串长度,如果为空,默认截取到字符串结尾; 如果n1=0 then n1=1 如果n1>0,则oracle从左向右确认起始位置截取 例如:SELECT SUBSTR('What is this',5,3) FROM DUAL; 如果n10,则oracle从右向左数确认起始位置 例如:SELECT SUBSTR('What is this',-5,3) FROM DUAL; 如果n1>c1.length则返回空 例如:SELECT SUBSTR('What is this',50,3) FROM DUAL; 然后再请你猜猜,如果n21,会如何返回值呢
11、TRANSLATE(c1,c2,c3) 就功能而言,此函数与replace有些相似。但需要注意的一点是,translate是绝对匹配替换,这点与replace函数具有非常大区别。什么是绝对匹配替换呢?简单的说,是将字符串c1中按一定的格式c2替换为c3。如果文字形容仍然无法理解,我们通过几具实例来说明: 例如: SELECT TRANSLATE('What is this','','-') FROM DUAL; SELECT TRANSLATE('What is this','-','') FROM DUAL; 结果都是空。来试试这个: SELECT TRANSLATE('What is this',' ',' ') FROM DUAL; 再来看这个: SELECT TRANSLATE('What is this','ait','-*') FROM DUAL; 是否明白了点呢?Replace函数理解比较简单,它是将字符串中指定字符替换成其它字符,它的字符必须是连续的。而translate中,则是指定字符串c1中出现的c2,将c2中各个字符替换成c3中位置顺序与其相同的c3中的字符。明白了?Replace是替换,而translate则像是过滤
(三).字符型函数返回数字值(Character Functions Returning Number Values) 本类函数支持所有的数据类型
1、ADD_MONTHS() 返回指定日期月份+n之后的值,n可以为任何整数。 例如:SELECT ADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12) FROM DUAL;
2、CURRENT_DATE 返回当前session所在时区的默认时间 例如: SQL> alter session set nls_date_format = 'mm-dd-yyyy' ; SQL> select current_date from dual;
3、SYSDATE 功能与上相同,返回当前session所在时区的默认时间。但是需要注意的一点是,如果同时使用sysdate与current_date获得的时间不一定相同,某些情况下current_date会比sysdate快一秒。经过与xyf_tck(兄台的大作ORACLE的工作机制写的很好,深入浅出)的短暂交流,我们认为current_date是将current_timestamp中毫秒四舍五入后的返回,虽然没有找到文档支持,但是想来应该八九不离十。同时,仅是某些情况下会有一秒的误差,一般情况下并不会对你的操作造成影响,所以了解即可。 例如:SELECT SYSDATE,CURRENT_DATE FROM DUAL;
4、LAST_DAY(d) 返回指定时间所在月的最后一天 例如:SELECT last_day(SYSDATE) FROM DUAL;
5、NEXT_DAY(d,n) 返回指定日期后第一个n的日期,n为一周中的某一天。但是,需要注意的是n如果为字符的话,它的星期形式需要与当前session默认时区中的星期形式相同。 例如:三思用的中文nt,nls_language值为SIMPLIFIED CHINESE SELECT NEXT_DAY(SYSDATE,5) FROM DUAL; SELECT NEXT_DAY(SYSDATE,'星期四') FROM DUAL; 两种方式都可以取到正确的返回,但是: SELECT NEXT_DAY(SYSDATE,'Thursday') FROM DUAL; 则会执行出错,提供你说周中的日无效,就是这个原因了。
1、TO_CHAR() 本函数又可以分三小类,分别是 转换字符->字符TO_CHAR(c):将nchar,nvarchar2,clob,nclob类型转换为char类型; 例如:SELECT TO_CHAR('AABBCC') FROM DUAL;
转换时间->字符TO_CHAR(d[,fmt]):将指定的时间(data,timestamp,timestamp with time zone)按照指定格式转换为varchar2类型; 例如:SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
转换数值->字符TO_CHAR(n[,fmt]):将指定数值n按照指定格式fmt转换为varchar2类型并返回; 例如:SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL;
2、TO_DATE(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2转换为日期类型,如果fmt参数不为空,则按照fmt中指定格式进行转换。注意这里的fmt参数。如果ftm为'J'则表示按照公元制(Julian day)转换,c则必须为大于0并小于5373484的正整数。 例如: SELECT TO_DATE(2454336, 'J') FROM DUAL; SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
为什么公元制的话,c的值必须不大于5373484呢?因为Oracle的DATE类型的取值范围是公元前4712年1月1日至公元9999年12月31日。看看下面这个语句: SELECT TO_CHAR(TO_DATE('9999-12-31','yyyy-mm-dd'),'j') FROM DUAL;
3、TO_NUMBER(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式转换为数值类型并返回。 例如:SELECT TO_NUMBER('-100.00', '9G999D99') FROM DUAL;
insert into tmp3 values ('','a001'); insert into tmp3 values ('','b001'); insert into tmp3 values ('a001','a002'); insert into tmp3 values ('a002','a004'); insert into tmp3 values ('a001','a003'); insert into tmp3 values ('a003','a005'); insert into tmp3 values ('a005','a008'); insert into tmp3 values ('b001','b003'); insert into tmp3 values ('b003','b005');
select lpad(' ', level*10,'=') ||'>'|| sys_connect_by_path(nodecol,'/') from tmp3 start with rootcol = 'a001' connect by prior nodecol =rootcol;
1、BIN_TO_NUM(n1,n2...n) 将一组位向量转换为等价的十进制形式。 例如:SELECT BIN_TO_NUM(1,1,0) FROM DUAL;
2、CAST(c as newtype) 将指定字串转换为指定类型,基本只对字符类型有效,比如char,number,date,rowid等。此类转换有一个专门的表列明了哪种类型可以转换为哪种类型,此处就不作酹述。 例如:SELECT CAST('1101' AS NUMBER(5)) FROM DUAL;
3、CHARTOROWID(c) 将字符串转换为rowid类型 例如:SELECT CHARTOROWID('A003D1ABBEFAABSAA0') FROM DUAL;
4、ROWIDTOCHAR(rowid) 转换rowid值为varchar2类型。返回串长度为18个字节。 例如:SELECT ROWIDTOCHAR(rowid) FROM DUAL;
5、TO_MULTI_BYTE(c) 将指定字符转换为全角并返回char类型字串 例如:SELECT TO_MULTI_BYTE('ABC abc 中华') FROM DUAL;
6、TO_SINGLE_BYTE(c) 将指定字符转换为半角并返回char类型字串 例如:SELECT TO_SINGLE_BYTE('ABC abc中华') FROM DUAL;
(六).其它辅助函数(Miscellaneous Single-Row Functions) 1、COALESCE(n1,n2,....n) 返回序列中的第一个非空值 例如:SELECT COALESCE(null,5,6,null,9) FROM DUAL;
如上所示,dump拥有不少参数。其本质是以指定格式,返回指定长度的exp的内部表示形式的varchar2值。fmt含4种格式:8||10||16||17,分别表示8进制,10进制,16进制和单字符,默认为10进制。start参数表示开始位置,length表示以,分隔的字串数。 例如:SELECT DUMP('abcdefg',17,2,4) FROM DUAL;