convert parts of string to int

  • 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[/url]

  • 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
  • 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
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • 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[/url]

  • Viewing 4 posts - 1 through 3 (of 3 total)

    You must be logged in to reply to this topic. Login to reply