Need to calculate the sum of second using a function from a string variable using SQL

Question

There is a column named as duration in a table called Adventurous.The column has values as below.In the column Suffix of 'H' is hours,Suffix of 'M' is minutes and Suffix of 'S' is seconds.How can we select the hours, minutes and seconds and convert all into seconds i.e sum of all the hours minutes and seconds in the form of seconds.

Duration 
--------
PT10M13S
PT13M22S
PT1H2M18S
PT11S

i tried using substring and charindex as below and tried to create a function but i am getting error:

Declare @Duration varchar(30) ='PT16H13M42S', @Dur varchar(10)
Declare @hours int
declare @mins int
declare @secs int
declare @len int

select @len = len(substring (@Duration, 3, len(@Duration))), @Dur=substring (@Duration, 3, len(@Duration))
select @hours = charindex('H', @Dur)

select substring(@Dur, 1, @hours-1)

select @Duration=substring (@Dur, @hours+1, len(@Dur))
select @mins = charindex('M', @Duration)

select substring(@Duration, 1, @mins-1)

select @Dur=substring (@Duration, @mins+1, len(@Duration))
select @secs= charindex('S', @Dur)

select substring(@Dur, 1, @Secs-1)

select @len, @Dur, @Duration

example PT1H2M18S= 1*3600+2*60+18=3738

Answer 1

Try this:

Declare @t table (duration varchar(50))

insert into @t values  ('PT1H2M18S')

select   
        convert(int,substring(duration,CHARINDEX('PT',duration)+2,(CHARINDEX('H',duration)-CHARINDEX('PT',duration))-2))*3600 +
        convert(int,substring(duration,CHARINDEX('H',duration)+1,(CHARINDEX('M',duration)-CHARINDEX('H',duration))-1))*60 +
        convert(int,substring(duration,CHARINDEX('M',duration)+1,(CHARINDEX('S',duration)-CHARINDEX('M',duration))-1))
        from @t

Answer 2

Another possible approach is to transform Duration text input into a valid T-SQL expression ('PT1H2M18S' will be transformed into '1*3600+2*60+18*1+0'). After that, consider next two options:

• Generate and execute a dynamic statement, which will evaluate each expression or

• Define a function to make the calculations

Input:

CREATE TABLE #Data (
    Duration varchar(50)
)
INSERT INTO #Data
    (Duration)
VALUES
    ('PT10M13S'),
    ('PT13M22S'),
    ('PT1H2M18S'),
    ('PT100H'),
    ('PT11S')

Dynamic statement:

DECLARE @stm nvarchar(max)
SET @stm = N''

SELECT @stm = @stm + 
    CONCAT(
        'UNION ALL SELECT ''',
        Duration,
        ''' AS [Duration], ', 
        REPLACE(REPLACE(REPLACE(REPLACE(Duration, 'H', '*3600+'), 'M', '*60+'), 'S', '*1+'), 'PT', ''), 
        '0 AS [Seconds] '
    )
FROM #Data
SET @stm = STUFF(@stm, 1, 10, N'')

EXEC (@stm)

User-defined function:

CREATE FUNCTION [udfCalculateHMS] (@expression varchar(100))
RETURNS int
AS
BEGIN
   DECLARE @result int
   DECLARE @s varchar(100)

   --
   SET @result = 0
   WHILE (CHARINDEX('+', @expression) > 0) BEGIN
      SET @s = SUBSTRING(@expression, 1, CHARINDEX('+', @expression) - 1)
      SET @expression = STUFF(@expression, 1, CHARINDEX('+', @expression), '')
      SET @result = @result + 
          CONVERT(int, SUBSTRING(@s, 1, CHARINDEX('*', @s) - 1)) * 
          CONVERT(int, STUFF(@s, 1, CHARINDEX('*', @s), ''))
   END

   -- Return value
   RETURN @result
END

SELECT 
    Duration, 
    dbo.udfCalculateHMS(CONCAT(REPLACE(REPLACE(REPLACE(REPLACE(Duration, 'H', '*3600+'), 'M', '*60+'), 'S', '*1+'), 'PT', ''), '0')) AS Seconds
FROM #Data

Output:

Duration    Seconds
PT10M13S    613
PT13M22S    802
PT1H2M18S   3738
PT100H      360000
PT11S       11