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


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 1,469, Visits: 2,775
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 @ 9:40 AM
Points: 3,937, Visits: 8,926
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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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: Yesterday @ 3:38 PM
Points: 1,805, Visits: 5,870
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


    UDP Broadcaster

    UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

    Group: General Forum Members
    Last Login: Today @ 9:33 AM
    Points: 1,469, Visits: 2,775
    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