Invalid length parameter error using SUBSTRING - SQL Server 2008 -


in first place, sorry amount of code i'm going post! :( stuck. have tried many things , none seems work. don't know error is...

i going explain function does. converts time values custom format seconds. if want know more, value represents duration of arrhytmic episode detected pacemaker. example, if duration 2 minutes , 35 seconds, custom format 'pt2m35s', , it's string.

my function (which created in vba , i'm trying migrate sql server) reads text char char, , depending of char different calculations. here have code converted sql function:

alter function pttoseconds(@duration varchar(30)) returns float begin     declare @posletrareciente int     declare @haycoma int     declare @poscoma int      set @posletrareciente = 1     set @haycoma = 0     set @poscoma = 0      declare @texto varchar(30)     set @texto = @duration      declare @tiempototal float     declare @factortiempo float     declare @incremento float      set @tiempototal = 0.0     set @factortiempo = 0     set @incremento = 0      declare @caracter varchar(30)     declare @counter int     set @counter = 1      declare @longtexto int     set @longtexto = len(@duration)      while @counter < @longtexto         begin         set @caracter = substring(@texto,@counter, 1)          set @haycoma = case @caracter                 when '.' 1                 else 0                 end          set @factortiempo = case                      when @caracter = 'd' 86400                     when @caracter = 'h' 3600                     when @caracter = 'm' 60                     when (@caracter = 's' , @haycoma = 1) (1 / (power(10,(@counter - (@poscoma + 1)))))                     when (@caracter = 's' , @haycoma = 0) 1                     when @caracter = 't' 0                     when @caracter = '.' 1                     else 0         end          set @posletrareciente = @counter          set @incremento = case                 when @caracter = 'd' cast((substring(@texto, (@posletrareciente + 1), @counter - (@posletrareciente + 1))) float) * @factortiempo                 when @caracter = 'h' cast((substring(@texto, (@posletrareciente + 1), @counter - (@posletrareciente + 1))) float) * @factortiempo                 when @caracter = 'm' cast((substring(@texto, (@posletrareciente + 1), @counter - (@posletrareciente + 1))) float) * @factortiempo                 when (@caracter = 's' , @haycoma = 1) cast((substring(@texto, (@posletrareciente + 1), @counter - (@posletrareciente + 1))) float) * @factortiempo                 when (@caracter = 's' , @haycoma = 0) cast((substring(@texto, (@posletrareciente + 1), @counter - (@posletrareciente + 1))) float) * @factortiempo                 when @caracter = 't' cast((substring(@texto, (@posletrareciente + 1), @counter - (@posletrareciente + 1))) float) * @factortiempo                 when @caracter = '.' cast((substring(@texto, (@posletrareciente + 1), @counter - (@posletrareciente + 1))) float) * @factortiempo                 else @factortiempo         end          set @tiempototal = @tiempototal + @incremento         set @counter = @counter + 1      end      return @tiempototal end

the error appears when calling substring. says following:

invalid length parameter passed left or substring function.

and don't know can causing it. tried use substring separatedly in query:

select x = substring('pt3m44s',2,1)

and works fine. mean error comes variable @counter, don't know why.

any orientation welcome.

thanks! i've tried specific can. if think haven't, please kind , tell why!

ignacio

you need rewrite logic.

i searched @posletrareciente in code , found this: set @posletrareciente = @counter

and expression @counter - (@posletrareciente + 1) gives -1.

and pass -1 third parameter in substring invalid


Comments

Post a Comment

Popular posts from this blog

Sort a complex associative array in PHP -

this question has answer here: php sort array subarray value 5 answers i have associative array in php like: $newarray1 = array ( [producta] => array ( [link] => http://sanjosespartan.com/blog/products/agile-java-development/ [visitcount] => 4 ) [productd] => array ( [link] => http://sanjosespartan.com/blog/products/intro-to-go/ [visitcount] => 1 ) [productg] => array ( [link] => http://sanjosespartan.com/blog/products/node-js-for-developers/ [visitcount] => 1 ) [productb] => array ( [link] => http://sanjosespartan.com/blog/products/beginning-mysql/ [visitcount] => 1 ) [productc] => array ( [link] => http://sanjosespartan.com/blog/products/computer-networks/ [visitcount] => 1 ) ...
Read more

vb.net - How to ignore if a cell is empty nothing -

how use roscolor() ignore value (like empty cell) ? color row if value upper 5 when there nothing in cell, want ignore roscolor() apply, how? public sub roscolor() integer = 0 quotedatagridview1.rows.count() - 1 step +1 dim val integer val = quotedatagridview1.rows(i).cells(3).value if val = vbempty quotedatagridview1.rows(i).defaultcellstyle.backcolor = color.white elseif val < 5 quotedatagridview1.rows(i).defaultcellstyle.backcolor = color.red elseif val > 5 , val < 10 quotedatagridview1.rows(i).defaultcellstyle.backcolor = color.lightyellow end if next end sub you can check empty value following public sub roscolor() integer = 0 quotedatagridview1.rows.count() - 1 step +1 dim val = quotedatagridview1.rows(i).cells(3).value if isdbnull(val) or val nothing quotedatagridview1.rows(i).defaultcellstyle.backcolor = color.white ...
Read more

recursion - Can every recursive algorithm be improved with dynamic programming? -

i first year undergraduate csc student looking competitive programming. recursion involves defining , solving sub problems. understand, top down dynamic programming (dp) involves memoizing solutions sub problems reduce time complexity of algorithm. can top down dp used improve efficiency of every recursive algorithm overlapping sub problems? dp fail work , how can identify this? the short answer is: yes. however, there constraints. obvious 1 recursive calls must overlap. i.e. during execution of algorithm, recursive function must called multiple times same parameters. lets truncate recursion tree memoization. can use memoization reduce number of calls. however, reduction of calls comes price. need store results somewhere. next obvious constraint need have enough memory. comes not-so obvious constraint. memory access requires time. first need find result stored , maybe copy location. in cases, might faster let recursion calculate result instead of loading somewher...
Read more