Converting English duration phrases to Numerical durations

Hey Everyone,

This is a small scoop of TSQL that converts an english duration phrase into numerical durations.
I created the required data with all possible combinations of the duration from days to seconds.

The script use "VALUES" keyword to create a derived table from which the numerical duration is extracted.

The total duration is calculated for seconds, minutes, hours and also days.
It could be included in a CTE or temp table as required.



SELECT DurationSQ

-- Extraction logic of the numerical duration

, CASE WHEN DurationSQ LIKE '%day%' THEN SUBSTRING(DurationSQ,1,CHARINDEX('Day',DurationSQ,1)-1) END AS Days

, CASE WHEN DurationSQ LIKE '%hour%' THEN SUBSTRING(DurationSQ,CHARINDEX('hour',DurationSQ,1)-2,2) END AS Hours

, CASE WHEN DurationSQ LIKE '%minute%' THEN SUBSTRING(DurationSQ,CHARINDEX('minute',DurationSQ,1)-2,2) END AS Minutes

, CASE WHEN DurationSQ LIKE '%second%' THEN SUBSTRING(DurationSQ,CHARINDEX('second',DurationSQ,1)-2,2) END AS Seconds

, (CASE WHEN DurationSQ LIKE '%day%' THEN SUBSTRING(DurationSQ,1,CHARINDEX('Day',DurationSQ,1)-1) END) * 24 * 60 *60 AS DaysInSec

, (CASE WHEN DurationSQ LIKE '%hour%' THEN SUBSTRING(DurationSQ,CHARINDEX('hour',DurationSQ,1)-2,2) END) * 60 * 60 AS HoursInSec

, (CASE WHEN DurationSQ LIKE '%minute%' THEN SUBSTRING(DurationSQ,CHARINDEX('minute',DurationSQ,1)-2,2) END) * 60 AS MinutesInSec

, ISNULL((CASE WHEN DurationSQ LIKE '%day%' THEN SUBSTRING(DurationSQ,1,CHARINDEX('Day',DurationSQ,1)-1) END) * 24 * 60 *60,0) --AS DaysInSec

+ ISNULL((CASE WHEN DurationSQ LIKE '%hour%' THEN SUBSTRING(DurationSQ,CHARINDEX('hour',DurationSQ,1)-2,2) END) * 60 * 60,0) --AS HoursInSec

+ ISNULL((CASE WHEN DurationSQ LIKE '%minute%' THEN SUBSTRING(DurationSQ,CHARINDEX('minute',DurationSQ,1)-2,2) END) * 60,0) --AS MinutesInSec

+ ISNULL((CASE WHEN DurationSQ LIKE '%second%' THEN SUBSTRING(DurationSQ,CHARINDEX('second',DurationSQ,1)-2,2) END),0) AS TotalDurationInSeconds

, ISNULL((CASE WHEN DurationSQ LIKE '%day%' THEN SUBSTRING(DurationSQ,1,CHARINDEX('Day',DurationSQ,1)-1) END) * 24 * 60,0) --AS DaysInMin

+ ISNULL((CASE WHEN DurationSQ LIKE '%hour%' THEN SUBSTRING(DurationSQ,CHARINDEX('hour',DurationSQ,1)-2,2) END) * 60,0) --AS HoursInMin

+ ISNULL((CASE WHEN DurationSQ LIKE '%minute%' THEN SUBSTRING(DurationSQ,CHARINDEX('minute',DurationSQ,1)-2,2) END),0) --AS Minutes

+ CONVERT(DECIMAL(5,2),ISNULL((CASE WHEN DurationSQ LIKE '%second%' THEN SUBSTRING(DurationSQ,CHARINDEX('second',DurationSQ,1)-2,2) END),0)/60.00) AS TotalDurationInMinutes

, ISNULL((CASE WHEN DurationSQ LIKE '%day%' THEN SUBSTRING(DurationSQ,1,CHARINDEX('Day',DurationSQ,1)-1) END) * 24,0) --AS DaysInHrs

+ ISNULL((CASE WHEN DurationSQ LIKE '%hour%' THEN SUBSTRING(DurationSQ,CHARINDEX('hour',DurationSQ,1)-2,2) END),0) --AS Hours

+ CONVERT(DECIMAL(5,2),ISNULL((CASE WHEN DurationSQ LIKE '%minute%' THEN SUBSTRING(DurationSQ,CHARINDEX('minute',DurationSQ,1)-2,2) END),0)/60.00) --AS MinutesinHrs

+ CONVERT(DECIMAL(5,2),ISNULL((CASE WHEN DurationSQ LIKE '%second%' THEN SUBSTRING(DurationSQ,CHARINDEX('second',DurationSQ,1)-2,2) END),0)/3600.00) AS TotalDurationInHours

, ISNULL((CASE WHEN DurationSQ LIKE '%day%' THEN SUBSTRING(DurationSQ,1,CHARINDEX('Day',DurationSQ,1)-1) END),0) --AS Days

+ CONVERT(DECIMAL(5,2),ISNULL((CASE WHEN DurationSQ LIKE '%hour%' THEN SUBSTRING(DurationSQ,CHARINDEX('hour',DurationSQ,1)-2,2) END),0)/24.00) --AS HoursInDays

+ CONVERT(DECIMAL(5,2),ISNULL((CASE WHEN DurationSQ LIKE '%minute%' THEN SUBSTRING(DurationSQ,CHARINDEX('minute',DurationSQ,1)-2,2) END),0)/(24*60.00)) --AS MinutesInDays

+ CONVERT(DECIMAL(5,2),ISNULL((CASE WHEN DurationSQ LIKE '%second%' THEN SUBSTRING(DurationSQ,CHARINDEX('second',DurationSQ,1)-2,2) END),0)/(24*3600.00)) AS TotalDurationInDays

FROM

-- Data preparation - creating a derived table using "VALUES" to host all possible combinations of the duration

(Values ('2days 8hours 5minutes 9seconds'),('2days 8hours 5minutes 1second'),('2days 8hours 1minute 9seconds'),('2days 8hours 1minute 1second'),('2days 1hour 5minutes 9seconds')

,('2days 1hour 5minutes 1second'),('2days 1hour 1minute 9seconds'),('2days 1hour 1minute 1second'),('2days 8hours'),('2days 1hour'),('2days 8hours 5minutes'),('2days 8hours 1minute')

,('2days 1hour 5minutes'),('2days 1hour 1minute'),('2days 8hours 9seconds'),('2days 8hours 1second'),('2days 1hour 9seconds'),('2days 1hour 1second'),('2days 5minutes 9seconds')

,('2days 5minutes 1second'),('2days 1minute 9seconds'),('2days 1minute 1second'),('2days 5minutes'),('2days 1minute'),('2days 9seconds'),('2days 1second'),('2days')

,('1day 8hours 5minutes 9seconds'),('1day 8hours 5minutes 1second'),('1day 8hours 1minute 9seconds'),('1day 8hours 1minute 1second'),('1day 1hour 5minutes 9seconds')

,('1day 1hour 5minutes 1second'),('1day 1hour 1minute 9seconds'),('1day 1hour 1minute 1second'),('1day 8hours'),('1day 1hour'),('1day 8hours 5minutes'),('1day 8hours 1minute')

,('1day 1hour 5minutes'),('1day 1hour 1minute'),('1day 8hours 9seconds'),('1day 8hours 1second'),('1day 1hour 9seconds'),('1day 1hour 1second'),('1day 5minutes 9seconds')

,('1day 5minutes 1second'),('1day 1minute 9seconds'),('1day 1minute 1second'),('1day 5minutes'),('1day 1minute'),('1day 9seconds'),('1day 1second'),('1day')

,('1hour 5minutes 9seconds'),('1hour 5minutes 1second'),('1hour 1minute 9seconds'),('1hour 1minute 1second'),('1hour 5minutes'),('1hour 1minute'),('1hour 9seconds'),('1hour 1second'),('1hour')

,('8hours 5minutes 9seconds'),('8hours 5minutes 1second'),('8hours 1minute 9seconds'),('8hours 1minute 1second'),('8hours 5minutes'),('8hours 1minute'),('8hours 9seconds'),('8hours 1second')

,('8hours')

,('5minutes 9seconds'),('5minutes 1second'),('5minutes'),('1minute 9seconds'),('1minute 1second'),('1minute')

,('9seconds'),('1second'),('12days'),('11hours'),('40minutes'),('53seconds')

,('10days 8hours 5minutes 9seconds'),('10days 8hours 5minutes 10seconds'),('10days 8hours 10minutes 9seconds'),('10days 8hours 10minutes 10seconds'),('10days 10hours 5minutes 9seconds')

,('10days 10hours 5minutes 10seconds'),('10days 10hours 10minutes 9seconds'),('10days 10hours 10minutes 10seconds'),('10days 8hours'),('10days 10hours'),('10days 8hours 5minutes')

,('10days 8hours 10minutes'),('10days 10hours 5minutes'),('10days 10hours 10minutes'),('10days 8hours 9seconds'),('10days 8hours 10seconds'),('10days 10hours 9seconds'),('10days 10hours 10seconds')

,('10days 5minutes 9seconds'),('10days 5minutes 10seconds'),('10days 10minutes 9seconds'),('10days 10minutes 10seconds'),('10days 5minutes'),('10days 10minutes'),('10days 9seconds')

,('10days 10seconds'),('10days'),('10hours 5minutes 9seconds'),('10hours 5minutes 10seconds'),('10hours 10minutes 9seconds'),('10hours 10minutes 10seconds'),('10hours 5minutes'),('10hours 10minutes')

,('10hours 9seconds'),('10hours 10seconds'),('10hours'),('8hours 5minutes 10seconds'),('8hours 10minutes 9seconds'),('8hours 10minutes 10seconds'),('8hours 10minutes'),('8hours 10seconds')

,('5minutes 10seconds'),('10minutes 9seconds'),('10minutes 10seconds'),('10minutes'),('10seconds')

) AS Times (DurationSQ)

See you back soon.

Bye
Sunil