将人类可读持续时间转换为时间格式 SQL SERVER 2014

Question

我有这个数据。

"2mn 56s", "30s 83ms", "2h 10mn"

如何将这些转换为时间格式：

hh:mn:ss - "00:02:56", "00:00:30", "02:10:00"

Answer 1

在 JavaScript 中，您可以使用String#replace方法。

var str = '"2mn 56s", "30s 83ms", "2h 10mn"';
console.log(
  str.replace(/"(?:(d)+hs*)?(?:(d+)mns*)?(?:(d+)ss*)?[^"]*"?/g, function(_, m1, m2, m3) {
    return [m1, m2, m3].map(v => ('00' +( v || '')).slice(-2)).join(':')
  })
)

---

正则表达式解释在这里。

Answer 2

我以后不会以这种格式存储TIME。话虽如此，根据您的示例数据，这里有一个 SQL Server 解决方案。取消注释掉@t1变量以查看不同的测试用例。

declare @t1 varchar(16)
--set @t1 = '2h 10mn'
set @t1 = '2mn 56s'
--set @t1 = '30s 83ms'
--this places your data in a time format. This is ideal in most cases so you can actually use datetime functions on the data
select
    case 
        when left(@t1, charindex(' ',@t1)) like '%h' 
        then convert(time,convert(varchar,left(@t1,charindex('h',@t1) - 1)) + ':' + substring(@t1,charindex(' ', @t1) + 1,len(@t1) - charindex(' ', @t1) - 2) + ':00')   
    end,
    case 
        when left(@t1, charindex(' ',@t1)) like '%mn' 
        then convert(time,'00:' + convert(varchar,left(@t1,charindex('mn',@t1) - 1)) + ':' + substring(@t1,charindex(' ', @t1) + 1,len(@t1) - charindex(' ', @t1) - 1))
    end,
    case 
        when left(@t1, charindex(' ',@t1)) like '%s'
        then convert(time,'00:00:' + convert(varchar,left(@t1,charindex('s',@t1) - 1)) + '.' + substring(@t1,charindex(' ', @t1) + 1,len(@t1) - charindex(' ', @t1) - 2))
    end

--if you only want it in the hh:mm:ss then you can use the below. This rounds your milliseconds properly
select
    case 
        when left(@t1, charindex(' ',@t1)) like '%h' 
        then convert(time(0),convert(varchar,left(@t1,charindex('h',@t1) - 1)) + ':' + substring(@t1,charindex(' ', @t1) + 1,len(@t1) - charindex(' ', @t1) - 2) + ':00')    
    end,
    case 
        when left(@t1, charindex(' ',@t1)) like '%mn' 
        then convert(time(0),'00:' + convert(varchar,left(@t1,charindex('mn',@t1) - 1)) + ':' + substring(@t1,charindex(' ', @t1) + 1,len(@t1) - charindex(' ', @t1) - 1))
    end,
    case 
        when left(@t1, charindex(' ',@t1)) like '%s'
        then convert(time(0),'00:00:' + convert(varchar,left(@t1,charindex('s',@t1) - 1)) + '.' + substring(@t1,charindex(' ', @t1) + 1,len(@t1) - charindex(' ', @t1) - 2))
    end