Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

convert parts of string to int Expand / Collapse
Author
Message
Posted Friday, May 2, 2014 2:57 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:07 PM
Points: 1,342, Visits: 2,512
I have log files that I am loading into a table with duration data in the format "xx hrs xx min xx sec". Only the parts that are required will be there so if duration is only 2 seconds , the column will show "2 sec".

I am trying to get the duration into in to do some analysis on it and I have come up with this query so far which returns the correct data but i am wondering if there is a way to do what I am trying in a more readable format.

CREATE TABLE #tmp(duration VARCHAR(20))

INSERT INTO #tmp
VALUES ('1 hrs 20 min 12 sec'), --4812 sec
('48 sec'), --48 sec
('39 min 1 sec'), --2341 sec
('11 hrs 1 min 1 sec'), --39661 sec
('59 min 0 sec'), --3540 sec
('2 min 50 sec') --170 sec

and this is what I have so far
SELECT CASE
WHEN CHARINDEX('hrs', duration, 1) <> 0 THEN CAST(SUBSTRING(duration, 1, CHARINDEX(' hrs', duration, 1) - 1)AS int) * 3600 + CAST(SUBSTRING(duration, CHARINDEX(' hrs', duration, 1) + 5, CHARINDEX(' min', duration, 1) - 7)AS int) * 60 + CAST(SUBSTRING(duration, CHARINDEX(' min', duration, 1) + 5, CHARINDEX(' sec', duration, 1) - 14)AS int)
WHEN CHARINDEX('hrs', duration, 1) = 0
AND CHARINDEX(' min', duration, 1) <> 0 THEN CAST(SUBSTRING(duration, 1, CHARINDEX(' min', duration, 1) - 1)AS int) * 60 + CAST(SUBSTRING(duration, CHARINDEX(' min', duration, 1) + 5, CHARINDEX(' sec', duration, 1) - 7)AS int)
WHEN CHARINDEX('hrs', duration, 1) = 0
AND CHARINDEX(' min', duration, 1) = 0 THEN SUBSTRING(duration, 1, CHARINDEX(' sec', duration, 1) - 1)
END AS duration from #tmp

I ultimately plan on converting this to a SSIS expression so that is why I am looking to simplify it.


Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1567183
Posted Friday, May 2, 2014 4:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:02 PM
Points: 3,313, Visits: 7,151
I'm not sure if this is considered simpler.

SELECT duration, SUM(iduration)
FROM (
SELECT duration,
Item * CASE ROW_NUMBER() OVER( PARTITION BY duration ORDER BY ItemNumber DESC)
WHEN 3 THEN 3600
WHEN 2 THEN 60
ELSE 1 END iduration
FROM #tmp x
CROSS APPLY dbo.PatternSplitCM( duration, '%[0-9]%')
WHERE Matched = 1) x
GROUP BY duration
ORDER BY duration

You can read about dbo.PatternSplitCM in here: http://www.sqlservercentral.com/articles/String+Manipulation/94365/



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1567194
Posted Friday, May 2, 2014 5:05 PM This worked for the OP Answer marked as solution


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:21 PM
Points: 1,780, Visits: 5,644
Another way....

select datediff(second,0,cast(replace(replace(replace(fix.duration,' hrs ',':'),' min ',':'),' sec','') as datetime))
from #tmp t
cross apply (
select
case
when len(t.duration)<8 then '0 hrs 0 min '
when len(t.duration)<15 then '0 hrs '
else ''
end + t.duration
) as fix(duration)



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1567197
    Posted Monday, May 5, 2014 9:01 AM


    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Today @ 1:07 PM
    Points: 1,342, Visits: 2,512
    Thank you both for your suggestions.

    Luis, The pattern splitter is going to be very useful for me, thanks for introducing me to it, but in this case I dont think I can use it because even though in SQL it works great, I dont think will be able to convert it to an SSIS expression. I would probably have to convert the logic in the function to a script task which is beyond my expertise at the moment. I will definitely get some use out of this function in other places.

    MM, this looks like an approach that I should be able to translate to an SSIS express(not sure if I can do anything with cross apply) and there is less logic to convert than my original.

    Thanks again.

    -edit: final SSIS expression using MM example
    DATEDIFF("ss",(DT_DBTIMEstamp)"1899/12/30",(DT_DBTIMEstamp)(REPLACE(REPLACE(REPLACE((LEN(SessionDuration) &lt; 8 ? "0 hrs 0 min " : LEN(SessionDuration) &lt; 15 ? "0 hrs " : "") + SessionDuration," hrs ",":")," min ",":")," sec","")))



    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help
    Post #1567554
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse