如何在小时、分钟和秒内解析SQL Server字符串

Question

我将时间存储为PT1H22M59.551S
读取时间为1小时22分59.551秒。

最有效的方法是什么？

数据可能是这样的。所以没有分离器是强制性的

PT0S
PT6H4M29.212S
PT0S
PT2M55.126S
PT54M4.12S
PT3H6M5.74S
PT16H27M52.069S

Answer 1

您还可以创建一个函数来执行具有基本文本操作的转换：

CREATE FUNCTION dbo.f_GetTime(@TimeInput VARCHAR(50)) RETURNS TIME
AS BEGIN
SET @TimeInput = REPLACE(REPLACE(@TimeInput, 'PT', ''), 'S', '')
IF CHARINDEX('H', @TimeInput) = 0 -- Add missing H
SET @TimeInput = '0H' + @TimeInput
IF CHARINDEX('M', @TimeInput) = 0 -- Add missing M
SET @TimeInput = REPLACE(@TimeInput, 'H', 'H0M')
RETURN CONVERT(TIME, REPLACE(REPLACE(@TimeInput, 'H', ':'), 'M', ':'))
END
GO

然后您可以将其仅用于标量查询：

SELECT dbo.f_GetTime('PT1H22M59.551S') AS TimeValue

或者你可以将它与表格一起使用：

DECLARE @Input TABLE (TimeInput VARCHAR(20))
INSERT @Input VALUES ('PT1H22M59.551S'), ('PT0S'), ('PT6H4M29.212S'), ('PT0S'),
('PT2M55.126S'), ('PT54M4.12S'), ('PT3H6M5.74S'), ('PT16H27M52.069S')
SELECT TimeInput, dbo.f_GetTime(TimeInput) AS TimeValue FROM @Input

这将返回：

TimeInput            TimeValue
-------------------- ----------------
PT1H22M59.551S       01:22:59.5510000
PT0S                 00:00:00.0000000
PT6H4M29.212S        06:04:29.2120000
PT0S                 00:00:00.0000000
PT2M55.126S          00:02:55.1260000
PT54M4.12S           00:54:04.1200000
PT3H6M5.74S          03:06:05.7400000
PT16H27M52.069S      16:27:52.0690000

Answer 2

--sql server
declare @data table(
lineid int identity, 
a varchar(100),
b varchar(100),
hr varchar(20),
[minute] varchar(20),
[second] varchar(20)
)
insert @data(a) select 'PT0S'
insert @data(a) select 'PT6H4M29.212S'
insert @data(a) select 'PT0S'
insert @data(a) select 'PT2M55.126S'
insert @data(a) select 'PT54M4.12S'
insert @data(a) select 'PT3H6M5.74S'
insert @data(a) select 'PT16H27M52.069S'
update @data
set b = replace(a,'PT','')
update @data
set hr = LEFT(b,charindex('H',b))
update @data
set b = REPLACE(b,hr,'')
update @data
set [minute] = LEFT(b,charindex('M',b))
update @data
set b = REPLACE(b,[minute],'')
update @data
set [second] = LEFT(b,charindex('S',b))
update @data
set b = REPLACE(b,[second],'')
update @data
set [hr] = REPLACE([hr],'H',''),[minute]=REPLACE([minute],'M',''),[second]=REPLACE([second],'S','')
update @data
set hr = isnull(nullif(hr,''),'0'),[minute] = isnull(nullif([minute],''),'0'),[second] = isnull(nullif([second],''),'0')
select *,
[hr] + ' hour'+ case when hr <> '1' then 's ' else ' ' end +
[minute] + ' minute'+ case when [minute] <> '1' then 's ' else ' ' end +
[second] + ' second(s)'
from @data

Answer 3

--sql server
declare @data table(
lineid int identity, 
[inboundTime] varchar(100)

)
insert @data([inboundTime]) select 'PT0S'
insert @data([inboundTime]) select 'PT6H4M29.212S'
insert @data([inboundTime]) select 'PT0S'
insert @data([inboundTime]) select 'PT2M55.126S'
insert @data([inboundTime]) select 'PT54M4.12S'
insert @data([inboundTime]) select 'PT3H6M5.74S'
insert @data([inboundTime]) select 'PT16H27M52.069S'
insert @data([inboundTime]) select 'PT52.069S'
update @data
set [inboundTime] = replace([inboundTime],'PT','')
SELECT  
INBOUND_H
,  INBOUND_M
,  INBOUND_S
,  INBOUND_H*3600 + INBOUND_M*60  inboundTimeMin
, [inboundTime]
from (
SELECT  
IIF (   CHARINDEX('H', [inboundTime]) > 0 ,   SUBSTRING([inboundTime],  0, CHARINDEX('H', [inboundTime])),0)  INBOUND_H
, IIF (   CHARINDEX('M', [inboundTime]) > 0 ,    SUBSTRING([inboundTime], CHARINDEX('H', [inboundTime])+1,CHARINDEX('M', [inboundTime]) - CHARINDEX('H', [inboundTime])-1 ), 0) INBOUND_M
, SUBSTRING([inboundTime], CHARINDEX('M', [inboundTime])+1,CHARINDEX('S', [inboundTime]) - CHARINDEX('M', [inboundTime])-1 ) INBOUND_S
, [inboundTime]

FROM @data)x

Answer 4

这是一个更健壮的功能构建解决方案：

CREATE FUNCTION dbo.f_GetTime(@TimeInput VARCHAR(50)) RETURNS VARCHAR(50)
AS BEGIN
SET @TimeInput = REPLACE(REPLACE(@TimeInput, 'PT', ''), 'S', '')
IF CHARINDEX('H', REVERSE(@TimeInput)) = 1
SET @TimeInput = @TimeInput + '00:00' -- Add missing S
IF CHARINDEX('M', REVERSE(@TimeInput)) = 1
SET @TimeInput = @TimeInput + '00' -- Add missing S
IF CHARINDEX('H', @TimeInput) = 0 -- Add missing H
SET @TimeInput = '00H' + @TimeInput
IF CHARINDEX('M', @TimeInput) = 0 -- Add missing M
SET @TimeInput = REPLACE(@TimeInput, 'H', 'H0M')
RETURN CONVERT(VARCHAR(50), REPLACE(REPLACE(@TimeInput, 'H', ':'), 'M', ':'))
END
GO