Evaluating a string arithmatic expression
-
Wow – I didn't expect to get this many responses to what I thought was a simple topic!
I finally came up with a workaround. I ended up using a combination of SUBSTRING, LEFT, and RIGHT functions. I used LEFT to get the hour (using CHARINDEX to find 'h', giving me my length), SUBSTRING to get the minutes, and RIGHT to get the seconds.
Here's what it looks like:
select * from
where
(convert(int, replace(left([Elapsed Time], charindex('h ',[Elapsed Time])),'h',''))
* 3600 +
convert(int, replace(substring([Elapsed Time],charindex('m ',[Elapsed Time])-2,3),'m',''))
* 60 +
convert(int, replace(right([Elapsed Time],3),'s',''))
)
<= [some seconds value input by user]
The charindex('m ',[Elapsed Time])-2 statement is dependent upon the fact that the minutes will never be more than two digits (although it may be one digit – single-digit minutes and seconds do not include a leading zero).
To answer one of the earlier posts, the field is of type VARCHAR. As for the format, it'll be Xh Ym Zs (where Y and Z is between 0 and 59 inclusive). If the value is less than one minute, you'll only see Zs; if less than an hour, you'll see Ym Zs, and if an hour or more, you'll see the full Xh Ym Zs.
I was hoping to find a more elegant solution with less overhead (I'm always looking for one). If anyone has anything better, I'm open to ideas.
Good to see all these suggestions – keep 'em coming!
-
--Here is the function I was thinking of writing. It seems to work pretty well. It could definitely be cleaned up since I wrote it pretty quickly.
if object_id('test') is not null
begin
drop table test
end
go
create table test (time varchar(20))
go
insert into test select '1h 10m 30s'
insert into test select '10m'
insert into test select '1h'
insert into test select '30s'
insert into test select '10m 30s'
insert into test select '1h 10m'
insert into test select '1h 30s'
go
if object_id('convert_to_seconds') is not null
drop function convert_to_seconds
go
create function convert_to_seconds (@time varchar(255))
returns int
as
begin
select @time = '* '+@time+' *'
declare@hours int,
@minutes int,
@seconds int,
@pointer1 int,
@pointer2 int,
@time_in_seconds int
select @hours = 0, @minutes = 0, @seconds = 0
select @pointer1 = charindex('h',@time)
if (@pointer1 > 0)
begin
if len(@time) > @pointer1--means we have a space
begin
select @pointer2 = charindex(' ',substring(reverse(@time),len(@time)-@pointer1+1,len(@time)))
if @pointer2 = 0 --means we do not have a space before hour section
begin
select @pointer2 = len(@time)
end
end
else
begin
select @pointer2 = len(@time)+1
end
select @hours = reverse(substring(substring(reverse(@time),len(@time)-@pointer1+1,len(@time)),2,@pointer2-2))
end
select@pointer1 = 0,
@pointer2 = 0
select @pointer1 = charindex('m',@time)
if (@pointer1 > 0)
begin
if len(@time) > @pointer1--means we have a space
begin
select @pointer2 = charindex(' ',substring(reverse(@time),len(@time)-@pointer1+1,len(@time)))
if @pointer2 = 0 --means we do not have a space before hour section
begin
select @pointer2 = len(@time)
end
end
else
begin
select @pointer2 = len(@time)+1
end
select @minutes = reverse(substring(substring(reverse(@time),len(@time)-@pointer1+1,len(@time)),2,@pointer2-2))
end
select@pointer1 = 0,
@pointer2 = 0
select @pointer1 = charindex('s',@time)
if (@pointer1 > 0)
begin
if len(@time) > @pointer1--means we have a space
begin
select @pointer2 = charindex(' ',substring(reverse(@time),len(@time)-@pointer1+1,len(@time)))
if @pointer2 = 0 --means we do not have a space before hour section
begin
select @pointer2 = len(@time)
end
end
else
begin
select @pointer2 = len(@time)+1
end
select @seconds = reverse(substring(substring(reverse(@time),len(@time)-@pointer1+1,len(@time)),2,@pointer2-2))
end
select @time_in_seconds = (@hours*3600)+(@minutes *60)+@seconds
return @time_in_seconds
end
go
select dbo.convert_to_seconds(time)
from test
-
nice one - efficient as well.
quote:
--Here is the function I was thinking of writing. It seems to work pretty well. It could definitely be cleaned up since I wrote it pretty quickly.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply