Oracle与MySql函数 .

Oracle:

 

1. 截取字符串中字符前的字符串(不包括字符)

select  Substr(P.SCHEDULE_CODE, 1, Instr(P.SCHEDULE_CODE, '(', 1)-1)  from M_SUB_TASK_SCHEDULE_LOG P;

 

2. 截取字符串中字符前的字符串(包括字符)

select Substr(P.SCHEDULE_CODE, 1, Instr(P.SCHEDULE_CODE, '(ret', 1))  from M_SUB_TASK_SCHEDULE_LOG P;

 

3.两个date格式的日期相减得时分秒

SELECT
   EXTRACT(DAY FROM (sysdate-to_date('2012-03-29 00:00:00','YYYY-MM-DD HH24:MI:ss')) DAY TO SECOND )
   || ' days '
   || EXTRACT(HOUR FROM (sysdate-to_date('2012-03-29 00:00:00','YYYY-MM-DD HH24:MI:ss')) DAY TO SECOND )
   || ' hours'
   || EXTRACT(MINUTE FROM (sysdate-to_date('2012-03-29 00:00:00','YYYY-MM-DD HH24:MI:ss')) DAY TO SECOND )
   || ' minute'
   || EXTRACT(SECOND FROM (sysdate-to_date('2012-03-29 00:00:00','YYYY-MM-DD HH24:MI:ss')) DAY TO SECOND )
   || ' second' "Interval"
FROM DUAL;

 

4.oracle整数或小数化为小数，且保留两位小数

SELECT ROUND(TO_CHAR(ROUND(16/36,2),'999,999,999.99'),2) FROM DUAL;

 

或

 

SELECT (--TO_NUMBER(SUBSTR(D21, 1, INSTR(D21, ' '))) ||
             SUBSTR(D21, INSTR(D21, ' ') + 1, 2) ||
             SUBSTR(D21, INSTR(D21, ' ') + 4, 2)||
             SUBSTR(D21, INSTR(D21, ' ') + 7, 2))TIME1,
             t.BEGIN_TIMESTAMP,
             t.END_TIMESTAMP
      FROM (SELECT CAST(P.END_TIMESTAMP AS TIMESTAMP) - CAST(P.BEGIN_TIMESTAMP AS TIMESTAMP) D21,
           BEGIN_TIMESTAMP,END_TIMESTAMP FROM M_SUB_TASK_SCHEDULE_LOG P)t ;

 

4.已知秒，转化成00:00:00的格式

 SELECT trunc(1123/(60*60))||':'||trunc((1123-3600*trunc(1123/(60*60)))/60) ||':'||trunc(1123-60*trunc((1123-3600*trunc(1123/(60*60)))/60)-3600*trunc(1123/(60*60))) RUNNING_TIME FROM DUAL;

 

5.求百分比，保留两位小数

select to_char(round(1/10000*100,2),'FM9999999999999990.00') as amount  from dual;

 

6.oracle时分秒转换函数或方法(时分秒112343怎么转换成11:23:43？)

SELECT substr(to_char(to_date('112233','HH24:MI:SS'),'yyyy-mm-ddHH24:mi:ss'),11) FROM DUAL;

 

MySql:

 

1.截取第一个 '(' 之前的所有字符(顺数)

SELECT SUBSTRING_INDEX(P.SCHEDULE_CODE, '(', 1) FROM M_SUB_TASK_SCHEDULE_LOG P;

 

2.截取第一个 '(' 之前的所有字符(倒数)

SELECT SUBSTRING_INDEX(P.SCHEDULE_CODE, '(', -1) FROM M_SUB_TASK_SCHEDULE_LOG P;

 

4.已知秒，转化成00:00:00的格式

  SELECT SEC_TO_TIME(1123);

 

5.两个timestamp格式的日期相减得时分秒

SELECT TIME_TO_SEC(TIMEDIFF(T3.END_TIMESTAMP, T3.BEGIN_TIMESTAMP)) FROM  M_SUB_TASK_SCHEDULE_LOG T3 ;（注：两日期格式要相同）

 

6.mysql整数或小数化为小数，且保留两位小数

SELECT ROUND((16+0.0)/32,2) FROM DUAL

 

7.mysql时分秒转换函数或方法(时分秒112343怎么转换成11:23:43？)

SELECT TIME_FORMAT('112233','%H:%m:%s')EDIT_TIME FROM DUAL;