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;

?

?

或

?

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 ;

?

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;