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 . . .
-
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