Evaluating a string arithmatic expression

  • HELP!

    I have a column called [Elapsed Time] that I need to compare against a numeric value. The column stores its values in the format Xh Ym Zs (where X, Y, and Z are values for hours, minutes, and seconds).

    Thus, the column stores values like 5s, 10m 2s, 5h 15m 35s, etc.

    I'm thinking the best way to perform my comparison is to convert to seconds. I'm using REPLACE to create a mathematical expression as follows:

    SELECT REPLACE(REPLACE(REPLACE([Elapsed Time], 'h ', ' * 3600 + '), 'm ', ' * 60 + '), 's', '') as [Elapsed Seconds]

    This gives me results that look like this:

    Elapsed Time    Elapsed Seconds

    ------------    ---------------

    5s                  5

    10m 2s           10 * 60 + 2

    5h 15m 35s      5 * 3600 + 15 * 60 + 35

    This is well and good, but now I need to evaluate the resulting arithmetic expression.

    To put it another way, if the arithmetic expression 5 * 5 returns 25, I need the STRING expression '5 * 5' to return 25, NOT '5 * 5'.

    Does anyone know how to do this? I've been trying to figure this out for the past couple of days, and I'm pulling my hair out!

    Thanks in advance for your help . . .

  • I think this might help you.

    declare @temp varchar(50),@sql varchar(100)

    set @temp='5h 15m 35s'

    set @sql='select '

    set @sql=@sql + REPLACE(REPLACE(REPLACE(@temp, 'h ', ' * 3600 + '), 'm ', ' * 60 + '), 's', '')

    exec (@sql)

    Result is:

    -----------

    18935

  • Thanks for the feedback; unfortunately, this doesn't help me.

    I need to eval **every value in the column** [Elapsed Time].

    My original post isn't entirely accurate, so let me clarify what I'm trying to do.

    What I REALLY need to do is a query similar to the following:

    select * from

    where [Elapsed Time] < [some time value]

    (or ">", or "=", or whatever comparison the user submits)

    Of course, in order to do the comparison, I need to compare apples to apples; otherwise, it'll do a string comparison (which I DON'T want it to do)!!! In order for me to perform a proper comparison, I need to convert my fields over to a common unit (in this case, seconds).

    So, I'm thinking that if my [Elapsed Time] column stores values of Xh Ym Zs, I can use REPLACE to create a mathematical statement to convert to seconds (X * 3600 + Y * 60 + Z).

    Of course, REPLACE returns a string. The question, therefore, is how do I execute the string.

    Your suggestion is okay for a few values, but I need to eval thousands of rows (over 90,000, at last count).

  • The easiest thing to do would be to create a three column table valued function that splits the time and converts to seconds. Then you could always add all three colums up. I didn't have time to write it, but I will if you still need help.

  • In which format are u storing the data in Elapsedtime column. Is it exactly the way u mentioned- like h m s?

  • I assumed that test table has values like this

    testid elapsedtime

    ----------- --------------------------------------------------

    1 5h 3m 5s

    2 5m 4s

    3 0h 0m 5s

    4 6s

    5 10m

    6 12h

    --- begin script

    create table #test (h varchar(10),m varchar(10),s varchar(10))

    insert into #test

    --- for hrs

    select

    (case when patindex('%h%',elapsedtime) > 0 then

    ltrim (left(elapsedtime, patindex('%h%',elapsedtime)-1 )

    )

    else '0' end) as h,

    --- for minutes

    (case when patindex('%m%',elapsedtime)> 0 and patindex('%h%',elapsedtime) > 0 then

    ltrim (substring(elapsedtime,patindex('%h%',elapsedtime)+1 , patindex('%m%',elapsedtime)-patindex('%h%',elapsedtime)-1 )

    )

    when patindex('%m%',elapsedtime) > 0 then

    ltrim (left(elapsedtime, patindex('%m%',elapsedtime)-1 )

    )

    else '0' end) as m,

    ---- for secs

    (

    case when patindex('%m%',elapsedtime)> 0 and patindex('%s%',elapsedtime) > 0 then

    ltrim (substring(elapsedtime,patindex('%m%',elapsedtime)+1 , patindex('%s%',elapsedtime)-patindex('%m%',elapsedtime)-1 )

    )

    when patindex('%h%',elapsedtime)> 0 and patindex('%s%',elapsedtime) > 0 then

    ltrim (substring(elapsedtime,patindex('%h%',elapsedtime)+1 , patindex('%s%',elapsedtime)-patindex('%s%',elapsedtime)-1 )

    )

    when patindex('%s%',elapsedtime) > 0 then

    ltrim (left(elapsedtime, patindex('%s%',elapsedtime)-1 )

    )

    else '0' end) as s

    from test

    select * from #test

    select * from #test where h*3600+m*60+s > yourtestvalue

    drop table #test

    --- end script

    Hope this might help!

  • Any change to get the producer of the data to write it consistently as with a h, a m and a s.

    Example: 0h 5m 0s or 0h 0m 2s

    Any change of creating an INT column and populate it during insert/updates with a trigger?

    declare @d varchar(20)

    Set @d='1h 5m 2s'

    Select DateDiff(s,'0:00',Replace(Replace(Replace(Replace(@d,'h',':'),'m',':'),'s',''),' ',''))

  • Hi Raymond Kim,

    well here's another approach.

    What about

    SELECT SUBSTRING(elapsedtime, CHARINDEX('h',elapsedtime)-1,1) *3600 +

    SUBSTRING(elapsedtime, CHARINDEX('m',elapsedtime)-2,2) *60 +

    SUBSTRING(elapsedtime, CHARINDEX('s',elapsedtime)-2,2)

    This returns

    NULL

    NULL

    18935

    in your example.

    The only thing that needs to be catched is if h has a value greater 9 and m and s have values < 10.

    Besides this, if possible you should consider to change the logic that writes these values into the table

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • oops,

    might be a little bit early today

    Returns also NULL for the second where it actually should return something. I'll keep thinking over this.

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Hi Raymond,

    this is growing really big!

    SELECT [ELAPSED_TIME] =

    CASE

    WHEN ((CHARINDEX('h',elapsedtime,0) > 0) AND (CHARINDEX('m',elapsedtime,0) > 0)) THEN

    SUBSTRING(elapsedtime, CHARINDEX('h',elapsedtime)-1,1) * 3600 +

    SUBSTRING(elapsedtime, CHARINDEX('m',elapsedtime)-2,2) * 60 +

    SUBSTRING(elapsedtime, CHARINDEX('s',elapsedtime)-2,2)

    WHEN ((CHARINDEX('h',elapsedtime,0) = 0) AND (CHARINDEX('m',elapsedtime,0) > 0)) THEN

    SUBSTRING(elapsedtime, CHARINDEX('m',elapsedtime)-2,2) * 60 +

    SUBSTRING(elapsedtime, CHARINDEX('s',elapsedtime)-2,2)

    WHEN ((CHARINDEX('h',elapsedtime,0) = 0) AND (CHARINDEX('m',elapsedtime,0) = 0)) THEN

    SUBSTRING(elapsedtime, CHARINDEX('s',elapsedtime)-1,1)

    END

    FROM stat

    The only thing that STILL needs to be catched is if h has a value greater 9 and m and s have values < 10. This will blow up this statement even more!

    Does this need to be done at db level. It might be easier to write a conversion routine at app level?

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Hey try this surreal solution. I doubt perfomance would justify it.

    SELECT ((CASE WHEN CHARINDEX('h',elapsedtime)>0 THEN CAST(LEFT(elapsedtime,CHARINDEX('h',elapsedtime)-1) AS int) ELSE 0 END) * 3600)+

    ((CASE WHEN CHARINDEX('m',elapsedtime)>0 THEN CAST(SUBSTRING(elapsedtime,(CASE WHEN CHARINDEX('h',elapsedtime)>0 THEN CHARINDEX('h',elapsedtime)+2 ELSE 1 END),CHARINDEX('m',elapsedtime)-(CASE WHEN CHARINDEX('h',elapsedtime)>0 THEN CHARINDEX('h',elapsedtime)+2 ELSE 1 END)) AS int) ELSE 0 END) * 60)+

    CAST(SUBSTRING(elapsedtime,(CASE WHEN CHARINDEX('m',elapsedtime)>0 THEN CHARINDEX('m',elapsedtime)+2 ELSE 1 END),CHARINDEX('s',elapsedtime)-(CASE WHEN CHARINDEX('m',elapsedtime)>0 THEN CHARINDEX('m',elapsedtime)+2 ELSE 1 END)) AS int)

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Hi David,

    quote:


    Hey try this surreal solution. I doubt perfomance would justify it.


    your solution has indeed something Dali would be proud of

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • does this qualify as surreal?

    /*

    assuming that all the elements are separated by a space ' ' and

    the string is left justified (does NOT begin with a space)this should give the

    correct answer for any combination of hours, minutes or seconds

    (1 to 3 elements in any order).

    Takes 5 secs for 14000 rows on a 500mhz P3

    create table tardis(testid int, elapsedtime char(20))

    insert into tardis values(1, '5h 3m 5s')

    insert into tardis values(2, '5m 4s')

    insert into tardis values(3, '0h 25m 15s')

    insert into tardis values(4, '6s')

    insert into tardis values(5, '10m')

    insert into tardis values(6, '12h')

    insert into tardis values(7, '1s 1m 1h')

    insert into tardis values(8, '100s 100m 100h')

    */

    select elapsedtime,

    -- first element

    substring(elapsedtime,0,

    charindex(' ',elapsedtime)-1)*

    (

    charindex('h',

    substring(elapsedtime,

    charindex(' ',elapsedtime)-1,1))*3600

    +

    charindex('m',

    substring(elapsedtime,

    charindex(' ',elapsedtime)-1,1))*60

    +

    charindex('s',

    substring(elapsedtime,

    charindex(' ',elapsedtime)-1,1))*1)

    +

    /* second element*/

    substring(substring(elapsedtime,charindex(' ',elapsedtime)+1,20),0,

    charindex(' ',substring(elapsedtime,charindex(' ',elapsedtime)+1,20))-1)*

    (

    charindex('h',

    substring(substring(elapsedtime,charindex(' ',elapsedtime)+1,20),

    charindex(' ',substring(elapsedtime,charindex(' ',elapsedtime)+1,20))-1,1))*3600

    +

    charindex('m',

    substring(substring(elapsedtime,charindex(' ',elapsedtime)+1,20),

    charindex(' ',substring(elapsedtime,charindex(' ',elapsedtime)+1,20))-1,1))*60

    +

    charindex('s',

    substring(substring(elapsedtime,charindex(' ',elapsedtime)+1,20),

    charindex(' ',substring(elapsedtime,charindex(' ',elapsedtime)+1,20))-1,1))*1)

    +

    /*third element*/

    substring(substring(substring(elapsedtime,charindex(' ',elapsedtime)+1,20),

    charindex(' ',substring(elapsedtime,charindex(' ',elapsedtime)+1,20))+1,20),0,

    charindex(' ',substring(substring(elapsedtime,charindex(' ',elapsedtime)+1,20),

    charindex(' ',substring(elapsedtime,charindex(' ',elapsedtime)+1,20))+1,20))-1)*

    (

    charindex('h',

    substring(substring(substring(elapsedtime,charindex(' ',elapsedtime)+1,20),

    charindex(' ',substring(elapsedtime,charindex(' ',elapsedtime)+1,20))+1,20),

    charindex(' ',substring(substring(elapsedtime,charindex(' ',elapsedtime)+1,20),

    charindex(' ',substring(elapsedtime,charindex(' ',elapsedtime)+1,20))+1,20))-1,1))*3600

    +

    charindex('m',

    substring(substring(substring(elapsedtime,charindex(' ',elapsedtime)+1,20),

    charindex(' ',substring(elapsedtime,charindex(' ',elapsedtime)+1,20))+1,20),

    charindex(' ',substring(substring(elapsedtime,charindex(' ',elapsedtime)+1,20),

    charindex(' ',substring(elapsedtime,charindex(' ',elapsedtime)+1,20))+1,20))-1,1))*60

    +

    charindex('s',

    substring(substring(substring(elapsedtime,charindex(' ',elapsedtime)+1,20),

    charindex(' ',substring(elapsedtime,charindex(' ',elapsedtime)+1,20))+1,20),

    charindex(' ',substring(substring(elapsedtime,charindex(' ',elapsedtime)+1,20),

    charindex(' ',substring(elapsedtime,charindex(' ',elapsedtime)+1,20))+1,20))-1,1))*1) seconds

    from tardis

  • This is getting really interesting.

    We should make a competition from it. Who's next?

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Nice one cql. Your choice of tardis appropriate here

    Not sure surreal, looks too pretty and structured for surreal, maybe cubist!

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 15 posts - 1 through 15 (of 17 total)

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