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

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,

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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);