如何将VARCHAR2类型时长值转换为INTERVAL类型？

针对你遇到的问题——无法直接将类似'-28:15:00'的VARCHAR2值转换为INTERVAL类型，以下是更简洁的解决方案，无需拆分字符串的小时、分钟、秒部分：

问题根源回顾 #

• INTERVAL关键字仅支持字符串字面量，不能直接跟列或表达式，因此select interval MY_VARCHAR hour to second from MY_TABLE会报ORA-00923错误。
• Oracle的CAST函数不支持直接将字符串转换为INTERVAL HOUR TO SECOND类型，仅支持完整的INTERVAL DAY TO SECOND或INTERVAL YEAR TO MONTH，所以直接CAST会报ORA-00963错误。

方案1：正则表达式转换格式（推荐） #

通过正则表达式将单独的小时:分钟:秒格式（含正负号）转换为天 小时:分钟:秒的格式，即可用CAST转换为INTERVAL DAY TO SECOND，支持负数和超过24小时的场景：

SELECT CAST(
    REGEXP_REPLACE(MY_VARCHAR, '^([+-]?)(\d+:\d+:\d+)$', '\10 \2')
    AS INTERVAL DAY TO SECOND
) FROM MY_TABLE;

正则说明：匹配字符串开头的正负号（可选），以及后续的时间部分，将其替换为「正负号+0 +时间部分」，例如'-28:15:00'会被替换为'-0 28:15:00'，符合INTERVAL DAY TO SECOND的格式要求。

方案2：条件拼接字符串 #

如果对正则不熟悉，也可以通过简单的条件拼接处理正负号，同样支持所有合法输入：

SELECT CAST(
    CASE 
        WHEN MY_VARCHAR LIKE '-%' THEN '-0 ' || SUBSTR(MY_VARCHAR, 2)
        ELSE '0 ' || MY_VARCHAR
    END AS INTERVAL DAY TO SECOND
) FROM MY_TABLE;

验证示例 #

测试负数和超24小时的场景：

-- 测试负数输入
SELECT CAST(REGEXP_REPLACE('-28:15:00', '^([+-]?)(\d+:\d+:\d+)$', '\10 \2') AS INTERVAL DAY TO SECOND) FROM DUAL;
-- 输出：-0 28:15:00.000000

-- 测试超24小时的正数输入
SELECT CAST(REGEXP_REPLACE('30:30:00', '^([+-]?)(\d+:\d+:\d+)$', '\10 \2') AS INTERVAL DAY TO SECOND) FROM DUAL;
-- 输出：+0 30:30:00.000000

内容的提问来源于stack exchange，提问作者makrom