# 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!

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.

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

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)