ORACLE常用函数

Oracle的substr函数简单用法

substr(字符串,截取开始位置,截取长度) //返回截取的字

substr('Hello World',0,1) //返回结果为 'H'  *从字符串第一个字符开始截取长度为1的字符串

substr('Hello World',1,1) //返回结果为 'H'  *0和1都是表示截取的开始位置为第一个字符

substr('Hello World',2,4) //返回结果为 'ello'

substr('Hello World',-3,3)//返回结果为 'rld' *负数(-i)表示截取的开始位置为字符串右端向左数第i个字符

测试：

select substr('Hello World',-3,3) value from dual;

 

附：java中substring(index1,index2)的简单用法

作用：从字符串索引(下标)为index1的字符开始截取长度为index2-index1 的字符串。

String str="Hello World";

System.out.println(str.substring(0,5));

打印结果为：Hello

 

instr()函数的格式  （俗称：字符查找函数）

解决Clob字段截取前两段坐标

SUBSTR(TO_CHAR(T.POINTS),0,INSTR(TO_CHAR(T.POINTS),',')-1) LONGTITUDE,

SUBSTR(TO_CHAR(POINTS),INSTR(TO_CHAR(T.POINTS),',')+1,INSTR(TO_CHAR(T.POINTS),',',2)-2) LATITUDE

这样有一个小问题，如果clob内容过长就无法to_char（），to_char（）只能4000长度 所以为了茁壮性可以先去截取一下clob 例如to_char(str(t.points,0,1000))

简化

  TO_CHAR(SUBSTR(T.POINTS,0,INSTR(T.POINTS,',')-1)) LONGTITUDE,
  TO_CHAR(SUBSTR(T.POINTS,INSTR(T.POINTS,',')+1,INSTR(T.POINTS,',',2)-2)) LATITUDE

 

格式一：instr( string1, string2 )    /   instr(源字符串, 目标字符串)

格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] )   /   instr(源字符串, 目标字符串, 起始位置, 匹配序号)

解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。

  注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。

2）实例

格式一

1 select instr('helloworld','l') from dual; --返回结果：3    默认第一次出现“l”的位置
2 select instr('helloworld','lo') from dual; --返回结果：4    即：在“lo”中，“l”开始出现的位置
3 select instr('helloworld','wo') from dual; --返回结果：6    即“w”开始出现的位置

格式二

 

1 select instr('helloworld','l',2,2) from dual;  --返回结果：4    也就是说：在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置
2 select instr('helloworld','l',3,2) from dual;  --返回结果：4    也就是说：在"helloworld"的第3(l)号位置开始，查找第二次出现的“l”的位置
3 select instr('helloworld','l',4,2) from dual;  --返回结果：9    也就是说：在"helloworld"的第4(l)号位置开始，查找第二次出现的“l”的位置
4 select instr('helloworld','l',-1,1) from dual;  --返回结果：9    也就是说：在"helloworld"的倒数第1(d)号位置开始，往回查找第一次出现的“l”的位置
5 select instr('helloworld','l',-2,2) from dual;  --返回结果：4    也就是说：在"helloworld"的倒数第1(d)号位置开始，往回查找第二次出现的“l”的位置
6 select instr('helloworld','l',2,3) from dual;  --返回结果：9    也就是说：在"helloworld"的第2(e)号位置开始，查找第三次出现的“l”的位置
7 select instr('helloworld','l',-2,3) from dual; --返回结果：3    也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第三次出现的“l”的位置

由此函数可以判断里面是否包含想要查找的内内容，判断大于0就可以。

 

 

WM_CONCAT函数

WM_CONCAT（）函数可以把查出来的数据多列用逗号隔开拼成一个字符串

例如

SELECT M.TASKNAME FROM VROADWORK_ROADWORK_MAIN M WHERE M.ID IN (1,2,3)

上面的语句是查询 id为1，2，3的任务名字

结果

但是用了WM_CONCAT（）函数

SELECT WM_CONCAT(M.TASKNAME) FROM VROADWORK_ROADWORK_MAIN M WHERE M.ID IN (1,2,3)

结果

嗯。。。。。不错

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

SELECT COLUMN_VALUE FROM TABLE(SPLIT('1,2,3',','))

这条语句可以把一个值为“1，2，3”的字符串分隔成三行 结果

这样写也可以

SELECT * FROM TABLE(SPLIT('1,2,3'))

测试这样写

SELECT * FROM (SELECT Split(M.DEPTS,',') TT FROM VROADWORK_ROADWORK_MAIN M WHERE M.ID = 38)

结果

这样写的话

SELECT * FROM TABLE(SELECT Split(M.DEPTS,',') TT FROM VROADWORK_ROADWORK_MAIN M WHERE M.ID = 38)

结果

实际视图应用

          TO_CHAR(T.QSSJ,'YYYY-MM-DD') QSSJ,
           TO_CHAR(T.JSSJ,'YYYY-MM-DD') JSSJ,
           T.DEPTS,
           (SELECT WM_CONCAT(SHORTNAME) FROM VMGR_DEPT WHERE DEPTNO IN (SELECT COLUMN_VALUE FROM TABLE(SPLIT(T.DEPTS,',')))) DEPTNAME,
           (SELECT WM_CONCAT(PARENTDEPT) FROM VMGR_DEPT WHERE DEPTNO IN (SELECT COLUMN_VALUE FROM TABLE(SPLIT(T.DEPTS,',')))) PARENTDEPT,

SQRT 函数 ：开平方（根号）

select  SQRT(4)  from dual;     --得2

POWER 函数 ：power(x,y) :计算x^y次方

select POWER (4,2) from dual;      --得16

replace字符串替换函数

replace(要操作的字段,'要替换的值','替换的值')

刚玩就碰到个小问题 把双引号替换成单引号，可以这么做

update T_SRC_GWXX t set t.workon_interval = replace(t.workon_interval,'"','''')  where t.id = 11

MOD函数

取余

将正常日期年月日转换成时间戳格式

select * from V_GCXX@IVMS i where i.jgsk between cast(to_date('2018-09-20 00:00:00','yyyy-mm-dd,hh24:mi:ss') as timestamp) and cast(to_date('2018-09-27 23:59:59','yyyy-mm-dd,hh24:mi:ss') as timestamp)

select cast(to_date('2016/8/19','yyyy-mm-dd,hh24:mi:ss') as timestamp) date_to_timestamp from dual; 

select TO_CHAR(to_timestamp('2016/8/19','yyyy-mm-dd hh24:mi:ss') ,'YYYY-MM-DD HH24:MI:SS') from dual