I used the following strategy:
1) Split the varchar using ":" delimiter.
select * from dbo.split('40:20',':')
This will return two rows. Row one contains 40 and row two contains 20.
2) Now multiply the first row with 60 to convert 40 hours into minutes and then add it with the value in the second row. i.e., (40*60)+20
The complete SQL query is as follows. It also includes the split function call.
The output of the above query execution is as follows:WITH items as(
select function_call.items, row_number() over (order by function_call.dummy) rownumber
from (
select items, 'dummy' dummy from dbo.split('40:20',':')) as function_call
)
select distinct ((cast((select items from items where rownumber = 1) as int)* 60) + cast((select items from items where rownumber = 2) as int)) minutes from items
I used the following split function, reused from another site:
CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))The split function can be independantly used as follows:
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
select * from dbo.split('40:20',':')


0 comments:
Post a Comment