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

score 2 · Answer 1 · answered May 13 '19 at 07:23

2

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

answered May 13 '19 at 07:23

Red Devil

2,343
2
21
41

1

Thank you so much for your kind help, however i suppose that if we have a value as 'PT12M18S' i guess it would not provide the desired result. I tried the same and came across an error – Pradyot Mohanty May 13 '19 at 10:18

Zhorov · Answer 2 · 2019-05-13T09:28:11.043

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

Thank you so very much for you time and help. Your suggestion has provided the solution. Thank you so much again — Pradyot Mohanty, May 13 '19 at 10:21
@PradyotMohanty Glad to help. If you think that this or any other answer is the best solution to your problem, you may accept it (only one asnwer can be accepted). Good luck! — Zhorov, May 13 '19 at 14:07

score 0 · Answer 3 · answered May 13 '19 at 07:15

This is how I would move across the string the pull out the correct integer values. The number of characters to offset may change depending on if you can have varying numbers of characters per hour, minute and second. But the principle should get you going.

Declare @Duration varchar(30) ='PT16H13M42S'


select * from 
(values(substring(@Duration,CHARINDEX('PT',@duration)+2,(CHARINDEX('H',@Duration)-CHARINDEX('PT',@Duration))-2),
        substring(@Duration,CHARINDEX('H',@duration)+1,(CHARINDEX('M',@Duration)-CHARINDEX('H',@Duration))-1),
        substring(@Duration,CHARINDEX('M',@duration)+1,(CHARINDEX('S',@Duration)-CHARINDEX('M',@Duration))-1))) duration ([Hours], [Minutes], [Seconds]);

score 0 · Answer 4 · answered May 13 '19 at 07:46

Throwing in an answer using Tally Table and mostly reliable ISNUMERIC SQL function

This should be good for small datasets. I also assume that you have valid numbers i.e. hour part are not >24, minute part or seconds part are not >60

create table #t(duration nvarchar(max));
insert into #t values
('PT10M13S')
,('PT13M22S')
,('PT1H2M18S')
,('PT11S')

select 
    totalseconds= sum(m.factor* case when ISNUMERIC(substring(duration, r-2,2))=1 then substring(duration, r-2,2) else substring(duration, r-1,1) end ), 
    duration from #t
cross join 
      (
       select r=row_number() over (order by (select NULL))-1
       from sys.objects s1 cross join sys.objects s2
)t
join 
(values('S',1),('M',60),('H',3600)) m(part,factor)
on r<=len(duration) and substring(duration, r,1) =m.part
group by duration

drop table #t

PS: See this SO link which suggests that scalar UDF are faster than ISNUMERIC Fastest way to check if a character is a digit?

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

4 Answers4