将VARCHAR2转换为Number [英] Convert VARCHAR2 into Number

问题描述

我有一个表，该表的列名为duration.其数据类型为VARCHAR2.

I have a table with a column named duration. Its data type is VARCHAR2.

我想对列duration求和.

00:56:30
02:08:40
01:01:00

总数=> 04:05:10

如何使用ANSI SQL或Oracle SQL执行此操作?

How can I do this using ANSI SQL or Oracle SQL?

推荐答案

您可以使用SUBSTR分隔小时，分钟和秒，然后将SUM分开，最后使用NUMTODSINTERVAL函数将其转换为类型.

You can separate the hours, mins and seconds using SUBSTR, then SUM it up and finally use NUMTODSINTERVAL function to convert it into INTERVAL type.

SELECT NUMTODSINTERVAL (SUM (total_secs), 'second')
  FROM (SELECT   SUBSTR (duration, 1, 2) * 3600
               + SUBSTR (duration, 4, 2) * 60
               + SUBSTR (duration, 7, 2) total_secs
          FROM user_tab);

这篇关于将VARCHAR2转换为Number的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！