将字符串转换为时间的SQL函数

Question

我有这个字符串:PT0H8M30S。 “H”前的数字代表小时，“M”前的数字代表分钟，“S”前的数字代表秒....如何使用 SQL 函数将其仅转换为分钟？
结果应该是:8.5分钟

非常感谢!!!

Answer 1

我们可以使用 SQL Server 中的基本字符串函数(PATINDEX、CHARINDEX 和 SUBSTRING)来解析您的时间字符串。要获得每个单元的结束点，我们可以查找H、M 或S。要获得起点，我们可以查找紧接在特定时间单位之前的一位或两位数字。这会导致一些冗长的 SQL Server 代码，但它对于以任何出现顺序存在/不存在的任何时间单位都相当稳健。

WITH yourTable AS (
    SELECT 'PTx10H48M30S' AS string UNION ALL
    SELECT 'P2DT3H4M'
)

SELECT
    CASE WHEN CHARINDEX('D', string) > 0
         THEN
             CASE WHEN PATINDEX('%[0-9][0-9]D%', string) > 0
                  THEN CONVERT(float, SUBSTRING(string, PATINDEX('%[0-9][0-9]D%', string), 2)) * 24 * 60
                  ELSE CONVERT(float, SUBSTRING(string, PATINDEX('%[0-9]D%', string), 1)) * 24 * 60 END
         ELSE 0.0 END
    +
    CASE WHEN CHARINDEX('H', string) > 0
         THEN
             CASE WHEN PATINDEX('%[0-9][0-9]H%', string) > 0
                  THEN CONVERT(float, SUBSTRING(string, PATINDEX('%[0-9][0-9]H%', string), 2)) * 60
                  ELSE CONVERT(float, SUBSTRING(string, PATINDEX('%[0-9]H%', string), 1)) * 60 END
         ELSE 0.0 END
    +
    CASE WHEN CHARINDEX('M', string) > 0
         THEN
              CASE WHEN PATINDEX('%[0-9][0-9]M%', string) > 0
                  THEN CONVERT(float, SUBSTRING(string, PATINDEX('%[0-9][0-9]M%', string), 2))
                  ELSE CONVERT(float, SUBSTRING(string, PATINDEX('%[0-9]M%', string), 1)) END
         ELSE 0.0 END
    +
    CASE WHEN CHARINDEX('S', string) > 0
         THEN
             CASE WHEN PATINDEX('%[0-9][0-9]S%', string) > 0
                  THEN CONVERT(float, SUBSTRING(string, PATINDEX('%[0-9][0-9]S%', string), 2)) / 60
                  ELSE CONVERT(float, SUBSTRING(string, PATINDEX('%[0-9]S%', string), 1)) / 60 END
         ELSE 0.0 END
FROM yourTable;

Demo