Convert/Format String to Int (Time to Hours)-CodePudding

Database Table

 ------------------------------------------- 
| Time                                      |
 ------------------------------------------- 
| 6 Days 12 Hours 35 Minutes 7 Seconds      |
| 4 Days 10 Hours 12 Minutes 0 Seconds      |
| 10 Hours 12 Minutes 0 Seconds             | 
| 12 Hours 35 Minutes 7 Seconds             | 
 -------------------------------------------

Create new column in MS SQL server as "Hours" and convert the "Time" column values to hours.

Example: if we convert "6 Days 12 Hours 35 Minutes 7 Seconds" manually to Hours it will be 156.58 Hours. We need to update Hours Column with respective hours.

 ------------------------------------------- ------------ 
| Time                                      | Hours      |
 ------------------------------------------- ------------ 
| 6 Days 12 Hours 35 Minutes 7 Seconds      |            |
| 4 Days 10 Hours 12 Minutes 0 Seconds      |            |
| 10 Hours 12 Minutes 0 Seconds             |            |
| 12 Hours 35 Minutes 7 Seconds             |            |
 ------------------------------------------- ------------

CodePudding user response：

A little ugly.

Example or dbFiddle

Declare @YourTable Table ([Time] varchar(50))
Insert Into @YourTable Values 
 ('6 Days 12 Hours 35 Minutes 7 Seconds')
,('4 Days 10 Hours 12 Minutes 0 Seconds')
,('10 Hours 12 Minutes 0 Seconds')
,('12 Hours 35 Minutes 7 Seconds')
 
Select A.*
      ,Hrs = (
       IsNull(try_convert(int,JSON_VALUE(JS,'$[0]')) * try_convert(int,JSON_VALUE(JS,'$[1]')),0)
       IsNull(try_convert(int,JSON_VALUE(JS,'$[2]')) * try_convert(int,JSON_VALUE(JS,'$[3]')),0)
       IsNull(try_convert(int,JSON_VALUE(JS,'$[4]')) * try_convert(int,JSON_VALUE(JS,'$[5]')),0)
       IsNull(try_convert(int,JSON_VALUE(JS,'$[6]')) * try_convert(int,JSON_VALUE(JS,'$[7]')),0)
      )/3600.0
  From @YourTable A
  Cross Apply (values ('["' replace(replace(string_escape(replace(replace(replace(replace([Time],'Days','86400'),'Hours','3600'),'Minutes','60'),'Seconds','1'),'json'),' ','_'),'_','","') '"]') ) B(JS)

Results

Time                                    Hrs
6 Days 12 Hours 35 Minutes 7 Seconds    156.585277
4 Days 10 Hours 12 Minutes 0 Seconds    106.200000
10 Hours 12 Minutes 0 Seconds           10.200000
12 Hours 35 Minutes 7 Seconds           12.585277