oracle 各种常用函数讲解
1. 连接字符串,用逗号隔开
--vmsys.wm_concat函数使用 如下所示,按部门进行分组,同一组的在一行中用逗号隔开
SELECT deptno, wmsys.wm_concat(distinict(ename)) FROM scott.emp GROUP BY deptno;
特点:(1)wm_concat 函数占用临时表空间
(2)这个函数最大支持30K
今天在测试库环境下用wm_concat查出来的数据是clob类型,因此要把clob转化为varchar2
to_char(substr(mw.context(name),0,1000))
注意:截取到3500的时候 就开始抛异常了
MySQL中group_concat函数连接字符串
完整的语法如下:
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
2. decode()函数
主要作用:将查询结果翻译成其他值(即以其他形式表现出来,以下举例说明);
使用方法:
Select decode(columnname,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
From talbename
Where …
其中columnname为要选择的table中所定义的column,
·含义解释:
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)的理解如下:
if (条件==值1)
then
return(翻译值1)
elsif (条件==值2)
then
return(翻译值2)
......
elsif (条件==值n)
then
return(翻译值n)
else
return(缺省值)
end if
注:其中缺省值可以是你要选择的column name 本身,也可以是你想定义的其他值,比如Other等;
3. NVL,NVL2,NULLIF,COALESCE
nvl 函数
NVL函数的格式如下:NVL(expr1,expr2)
含义是:如果oracle第一个参数为空那么显示第二个参数的值,如果第一个参数的值不为空,则显示第一个参数本来的值。
select ename,NVL(comm, -1) from scott.emp;
nvl2 函数
NVL2函数的格式如下:NVL2(expr1,expr2, expr3)
含义是:如果该函数的第一个参数为空那么显示第二个参数的值,如果第一个参数的值不为空,则显示第三个参数的值。
nullif函数
NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL),否则返回第一个值。
select nullif(2,1) from dual ---> 2
select nullif(1,1) from dual ----> 空
Coalesce函数
Coalese函数的作用是的NVL的函数有点相似,其优势是有更多的选项。
格式如下:
Coalesce(expr1, expr2, expr3….. exprn)
表示可以指定多个表达式的占位符。所有表达式必须是相同类型,或者可以隐性转换为相同的类型。
返回表达式中第一个非空表达式,如有以下语句: SELECT COALESCE(NULL,NULL,3,4,5) FROM dual 其返回结果为:3
如果所有自变量均为 NULL,则 COALESCE 返回 NULL 值。 COALESCE(expression1,...n) 与此 CASE 函数等价:
这个函数实际上是NVL的循环使用,在此就不举例子了。
4. instr 函数
语法如下:
instr( string1, string2, start_position,nth_appearance ) [1] [2]
string1 | 源字符串,要在此字符串中查找。 |
string2 | 要在string1中查找的字符串 。 |
start_position | 代表string1 的哪个位置开始查找。此参数可选,如果省略默认为1. 字符串索引从1开始。如果此参数为正,从左到右开始检索,如果此参数为负,从右到左检索,返回要查找的字符串在源字符串中的开始索引。 |
nth_appearance | 代表要查找第几次出现的string2. 此参数可选,如果省略,默认为 1.如果为负数系统会报错。 |
注意:
位置索引号从1开始。
如果String2在String1中没有找到,instr函数返回0。
示例:
SELECT instr('syranmo','s') FROM dual; -- 返回 1
SELECT instr('syranmo','ra') FROM dual; -- 返回 3
SELECT instr('syran mo','a',1,2) FROM dual; -- 返回 0
5 substr 函数
SUBSTR(string,start_position,[length]) 求子字符串,返回字符串
解释:string 元字符串
start_position 开始位置(从0开始)
length 可选项,子字符串的个数
For example:
substr("ABCDEFG", 0); //返回:ABCDEFG,截取所有字符
substr("ABCDEFG", 2); //返回:CDEFG,截取从C开始之后所有字符
substr("ABCDEFG", 0, 3); //返回:ABC,截取从A开始3个字符
substr("ABCDEFG", 0, 100); //返回:ABCDEFG,100虽然超出预处理的字符串最长度,但不会影响返回结果,系统按预处理字符串最大数量返回。
substr("ABCDEFG", -3); //返回:EFG,注意参数-3,为负值时表示从尾部开始算起,字符串排列位置不变。
6. case when then else end
case when then else end 可以在查询列中使用,也可以在where条件中使用
如:
[sql] view plain copy
1. String sql =" SELECT AER.RES_ID,AER.PATIENT_ID,AER.EVENT_NO,P.NAME,AER.OUT_DEPT_NAME,"
2. +" CASE WHEN (AER.TYPE=4 AND AR.HIS_YL_PROCESS =2) THEN AR.HIS_YL_SCORE END HIS_YL_SCORE,"
3. +" CASE WHEN (AER.TYPE=7 AND AR.HIS_MZ_PROCESS =2) THEN AR.HIS_MZ_SCORE END HIS_MZ_SCORE,"
4. +" CASE WHEN (AER.TYPE=4 AND AR.HIS_YL_PROCESS =2) THEN AR.HIS_YL_LEVEL END HIS_YL_LEVEL,"
5. +" CASE WHEN (AER.TYPE=7 AND AR.HIS_MZ_PROCESS =2) THEN AR.HIS_MZ_LEVEL END HIS_MZ_LEVEL,"
6. +" CASE WHEN (AR.ONLINE_PROCESS =2) THEN AR.ONLIN_SCORE END ONLIN_SCORE,"
7. +" CASE WHEN (AR.ONLINE_PROCESS =2) THEN AR.ONLINE_LEVEL END ONLINE_LEVEL"
8. +" FROM AUDIT_EMR_REPAIR AER "
9. +" INNER JOIN AUDIT_RESULT AR ON AER.RES_ID = AR.RES_ID"
10. +" INNER JOIN AUDIT_RESULT_DETAIL ARD ON ARD.RES_ID = AER.RES_ID"
11. +" INNER JOIN EMR_CONTENT EC ON EC.EMR_ID = ARD.EMR_ID"
12. +" INNER JOIN PATIENT P ON P.PATIENT_ID=AER.PATIENT_ID"
13. +" WHERE AER.REPAIR_STATUS = 1 AND AER.ISSUE_STATUS = 1 AND AER.TYPE=4 AND ARD.TYPE IN (1,4) " //AER.TYPE IN(4,7) AND ARD.TYPE IN (1,4,7)
14. +" AND (CASE WHEN (EC.MODIFIOR_ID ='' or EC.MODIFIOR_ID is null) THEN EC.CREATOR_ID ELSE EC.MODIFIOR_ID END) = '"+doctorId+"'"
7. to_date to_char
这两个函数是非常重要,而且用的比较多。
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual --字符串转换为日期
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; --日期转化为字符串
select to_char(to_date('2015-1-25','yyyy-mm-dd'),'day') from dual; --求某天是星期几
8. 字符串连接
oracle 连接字符串有两种方法:(1) || (2)concat
select '您好:' || 28 as text from dual;
select concat('您好:',28) as text from dual;
9. 字符串长度 length() 和 lengthb()
lengthb(string)计算string所占的字节长度 :返回字符串的长度,单位是字节
length(string)计算string所占的字符长度 :返回字符串的长度,单位是字符
select length('国') from dual; --1
select lengthb('国') from dual; --2
select length('AB') from dual; --2
select lengthb('AB') from dual; --2
10 大小写转换
lower() 转化为小写
upper() 转化为大写
select lower('ADFDc') as text from dual; --adfdc
select upper('adfDFDF') as text from dual; --ADFDFDF
11--Oracle trunc()函数的用法
/**************日期********************/
1.select trunc(sysdate) from dual --2011-3-18 今天的日期为2011-3-18
2.select trunc(sysdate, 'mm') from dual --2011-3-1 返回当月第一天.
3.select trunc(sysdate,'yy') from dual --2011-1-1 返回当年第一天
4.select trunc(sysdate,'dd') from dual --2011-3-18 返回当前年月日
5.select trunc(sysdate,'yyyy') from dual --2011-1-1 返回当年第一天
6.select trunc(sysdate,'d') from dual --2011-3-13 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual --2011-3-18 14:00:00 当前时间为14:41
8.select trunc(sysdate, 'mi') from dual --2011-3-18 14:41:00 TRUNC()函数没有秒的精确
/***************数字********************/
/*
TRUNC(number,num_digits)
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
*/
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual --123.458
15.select trunc(123) from dual --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120
12 replace 函数用法如下:
replace('将要更改的字符串','被替换掉的字符串','替换字符串')
例:select replace('AABBCCDD','BB','FF') as text from dual; --AAFFCCDD
详情请见:https://blog.csdn.net/zhixinhuacom/article/details/48271435