How to extract day, hour, and minute from a string in SQL Server

Question

I have a column that contains values as below:

Duration
--------
minute45hour1day0
minute8hour10day2
minute15hour2day11
...

I want to extract day, hour, and minute to a different column:

day | hour | minutes
----+------+--------
 0  |  1   | 45
 2  | 10   |  8
11  |  2   | 15

To get the day I tired:

LEFT(StageDuration, CHARINDEX('day', Duration) - 1) AS day,

Does this answer your question? [Get everything after and before certain character in SQL Server](https://stackoverflow.com/questions/11010453/get-everything-after-and-before-certain-character-in-sql-server) — Ibrennan208, Oct 06 '22 at 07:46
@Ibrennan208 your suggested answer is different than my question, in my case the number of minutes, hours and days can be 1 or two characters it's not static. if you think there is a way around it, please add a response. — Guissous Allaeddine, Oct 06 '22 at 08:28

score 3 · Accepted Answer · answered Oct 06 '22 at 10:13

Here is my solution :

SELECT
REVERSE(PARSENAME(REVERSE(REPLACE( REPLACE( replace('minute45hour1day0','minute',''),'hour' ,'.') , 'day','.')), 1)) AS minute ,
REVERSE(PARSENAME(REVERSE(REPLACE( REPLACE( replace('minute45hour1day0','minute',''),'hour' ,'.') , 'day','.')), 2)) AS hour ,
REVERSE(PARSENAME(REVERSE(REPLACE( REPLACE( replace('minute45hour1day0','minute',''),'hour' ,'.') , 'day','.')), 3)) AS day

Also please check for PARSENAME to undestand the Logic

REVERSE is simple. Ex: ali -> ila

Yes +1 . Available from 2014 and higher version. – Roshan Nuvvula Oct 06 '22 at 10:20 — Roshan Nuvvula, Oct 06 '22 at 10:20

score 2 · Answer 2 · answered Oct 06 '22 at 10:07

You can try below code

declare @string Varchar(50) = 'minute45hour1day0'
--declare @string Varchar(50) = 'minute8hour10day2'
--declare @string Varchar(50) = 'minute15hour2day11'

select CASE WHEN CHARINDEX('minute',@string)>0 AND ISNUMERIC(SUBSTRING(@string,CHARINDEX('minute',@string)+6,2))=1  
                  THEN SUBSTRING(@string,CHARINDEX('minute',@string)+6,2) 
                  ELSE SUBSTRING(@string,CHARINDEX('minute',@string)+6,1) 
            END AS [minutes]
     ,CASE WHEN CHARINDEX('hour',@string)>0 AND ISNUMERIC(SUBSTRING(@string,CHARINDEX('hour',@string)+4,2))=1  
                  THEN SUBSTRING(@string,CHARINDEX('hour',@string)+4,2) 
                  ELSE SUBSTRING(@string,CHARINDEX('hour',@string)+4,1) 
            END AS [hour]
     ,CASE WHEN CHARINDEX('day',@string)>0 AND ISNUMERIC(SUBSTRING(@string,CHARINDEX('day',@string)+3,3))=1  
                  THEN SUBSTRING(@string,CHARINDEX('day',@string)+3,2)
                  ELSE SUBSTRING(@string,CHARINDEX('day',@string)+3,1) 
            END AS [day]

You can change the logic to handle if the day count is more than 2 digits.

I do echo if you have sql server 14+ version – Roshan Nuvvula Oct 07 '22 at 02:06 — Roshan Nuvvula, Oct 07 '22 at 02:06

score 1 · Answer 3 · answered Oct 06 '22 at 10:54

I'd go a slightly different route to the other commenters and suggest creating a function that handles this logic, as it's something that you'll probably want to use in a few different queries will eventually and -- when you upgrade to newer versions of SQL Server -- have more efficient means of dealing with.

So, I'd create a function like:

CREATE OR ALTER FUNCTION dbo.DurationSplit(@Duration VARCHAR(50))
RETURNS @R TABLE ( [Days] INT, [Hours] INT, [Minutes] INT)
AS BEGIN
    DECLARE @Days INT, @Hours INT, @Minutes INT;

    SET @Duration = REPLACE(@Duration, 'day', '.');
    SET @Duration = REPLACE(@Duration, 'hour', '|');
    SET @Duration = REPLACE(@Duration, 'minute', '');

    SET @Minutes = CAST(
        SUBSTRING(@Duration, 0, CHARINDEX('|', @Duration, 0))
    AS INT);
    SET @Duration = SUBSTRING(@Duration, CHARINDEX('|', @Duration, 0)+1, 50);
    SET @Hours = CAST(
        SUBSTRING(@Duration, 0, CHARINDEX('.', @Duration, 0))
    AS INT);
    SET @Duration = SUBSTRING(@Duration, CHARINDEX('.', @Duration, 0)+1, 50);
    SET @Days = CAST(@Duration AS INT);
    
    INSERT @R ([Days], [Hours], [Minutes])
        VALUES (@Days, @Hours, @Minutes);

    RETURN;
END
GO

which then you can include in your queries like so:

CREATE TABLE #Data (
    Duration VARCHAR(50)
);

INSERT #Data (Duration) VALUES ('minute45hour1day0'), ('minute8hour10day2'), ('minute15hour2day11');

SELECT * 
FROM #Data d
CROSS APPLY dbo.DurationSplit(d.Duration);

How to extract day, hour, and minute from a string in SQL Server

3 Answers3