Here is another version I posted for you here
https://techcommunity.microsoft.com/t5/sql-server/string-column-values-convert-into-hours/m-p/2688949
DECLARE @tmp TABLE
(
Ticket_Number nvarchar(100)
,TotalDuration varchar(100)
)
INSERT INTO @tmp VALUES
('JIRA123','2 Days 10 Hours 15 Minutes')
,('JIRA456','0 days 1 hour 12 minutes')
,('JIRA999','45 minutes')
,('JIRA888','13 minutes')
,('JIRA777','null')
,('JIRA666','15 days 17 hours 45 minutes')
,('JIR???','17 hours 45 minutes') -- added, to prove pattern working
SELECT
t.*
,clean.TotalDuration
,cleaned.TotalDuration
-- this is what you want
,Time_Hours = NULLIF(CAST(
ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 3) as decimal(9,2)) * 24, 0) -- days
+ ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 2) as decimal(9,2)), 0) -- hours
+ ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 1) as decimal(9,2)) / 60.0, 0) -- minutes
as decimal(9,2)), 0)
FROM @tmp t
CROSS APPLY(SELECT TotalDuration = CASE WHEN t.TotalDuration != 'null' THEN REPLACE(REPLACE(t.TotalDuration, ' hour ', ' hours '), 'null', '') END) clean
CROSS APPLY(SELECT TotalDuration = '"' + REPLACE(REPLACE(REPLACE(clean.TotalDuration, 'hours', '"."'), 'days', '"."'), 'minutes', '"')) cleaned
or simpler
SELECT
t.*
,clean.TotalDuration
,cleaned.TotalDuration
-- this is what you want
,Time_Hours = NULLIF(CAST(
ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 3) as decimal(9,2)) * 24, 0) -- days
+ ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 2) as decimal(9,2)), 0) -- hours
+ ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 1) as decimal(9,2)) / 60.0, 0) -- minutes
as decimal(9,2)), 0)
FROM @tmp t
CROSS APPLY(SELECT TotalDuration = REPLACE(t.TotalDuration, ' hour ', ' hours ')) clean
CROSS APPLY(SELECT TotalDuration = REPLACE(REPLACE(REPLACE(clean.TotalDuration, 'hours', '.'), 'days', '.'), 'minutes', '')) cleaned
or another variation... you need to test performance of these to know what is better
SELECT
t.*
,clean.TotalDuration
,cleaned.TotalDuration
-- this is what you want
,Time_Hours = NULLIF(CAST(
ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 3) as decimal(9,2)) * 24, 0) -- days
+ ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 2) as decimal(9,2)), 0) -- hours
+ ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 1) as decimal(9,2)) / 60.0, 0) -- minutes
as decimal(9,2)), 0)
FROM @tmp t
CROSS APPLY(SELECT TotalDuration = REPLACE(t.TotalDuration, ' hour ', ' hours ')) clean
CROSS APPLY(SELECT TotalDuration = TRANSLATE(clean.TotalDuration, 'dayshoursminutes', '. . ')) cleaned