SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


convert parts of string to int


convert parts of string to int

Author
Message
Robert klimes
Robert klimes
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3175 Visits: 3451
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
Luis Cazares
Luis Cazares
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22842 Visits: 19391
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
mister.magoo
mister.magoo
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5504 Visits: 7871
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Robert klimes
    Robert klimes
    Hall of Fame
    Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

    Group: General Forum Members
    Points: 3175 Visits: 3451
    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) < 8 ? "0 hrs 0 min " : LEN(SessionDuration) < 15 ? "0 hrs " : "") + SessionDuration," hrs ",":")," min ",":")," sec","")))



    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search