Invalid length parameter error using SUBSTRING - SQL Server 2008

Question

In first place, sorry for the amount of code I'm going to post! :( But I am stuck. I have tried many things and none seems to work. I don't really know where the error is...

I am going to explain what this function does. It converts time values from a custom format to only seconds. If you want to know more, the value represents the duration of an arrhytmic episode detected by a pacemaker. For example, if the duration is 2 minutes and 35 seconds, the custom format would be 'PT2M35S', and it's a string.

My function (which was originally created in VBA and now I'm trying to migrate to SQL Server) reads the text char by char, and depending of the char it does different calculations. Here you have the code converted to an SQL function:

ALTER FUNCTION PTtoSeconds(@duration varchar(30))
RETURNS FLOAT
AS
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 '.' THEN 1
                ELSE 0
                END

        SET @factorTiempo = CASE 
                    WHEN @caracter = 'D' THEN 86400
                    WHEN @caracter = 'H' THEN 3600
                    WHEN @caracter = 'M' THEN 60
                    WHEN (@caracter = 'S' AND @hayComa = 1) THEN (1 / (POWER(10,(@counter - (@posComa + 1)))))
                    WHEN (@caracter = 'S' AND @hayComa = 0) THEN 1
                    WHEN @caracter = 'T' THEN 0
                    WHEN @caracter = '.' THEN 1
                    ELSE 0
        END

        SET @posLetraReciente = @counter

        SET @incremento = CASE
                WHEN @caracter = 'D' THEN CAST((SUBSTRING(@texto, (@posLetraReciente + 1), @counter - (@posLetraReciente + 1))) as FLOAT) * @factorTiempo
                WHEN @caracter = 'H' THEN CAST((SUBSTRING(@texto, (@posLetraReciente + 1), @counter - (@posLetraReciente + 1))) as FLOAT) * @factorTiempo
                WHEN @caracter = 'M' THEN CAST((SUBSTRING(@texto, (@posLetraReciente + 1), @counter - (@posLetraReciente + 1))) as FLOAT) * @factorTiempo
                WHEN (@caracter = 'S' AND @hayComa = 1) THEN CAST((SUBSTRING(@texto, (@posLetraReciente + 1), @counter - (@posLetraReciente + 1))) as FLOAT) * @factorTiempo
                WHEN (@caracter = 'S' AND @hayComa = 0) THEN CAST((SUBSTRING(@texto, (@posLetraReciente + 1), @counter - (@posLetraReciente + 1))) as FLOAT) * @factorTiempo
                WHEN @caracter = 'T' THEN CAST((SUBSTRING(@texto, (@posLetraReciente + 1), @counter - (@posLetraReciente + 1))) as FLOAT) * @factorTiempo
                WHEN @caracter = '.' THEN CAST((SUBSTRING(@texto, (@posLetraReciente + 1), @counter - (@posLetraReciente + 1))) as FLOAT) * @factorTiempo
                ELSE @factorTiempo
        END

        SET @tiempoTotal = @tiempoTotal + @incremento
        SET @counter = @counter + 1

    END

    RETURN @tiempoTotal
END

The error appears when calling SUBSTRING. It says the following:

Invalid length parameter passed to the LEFT or SUBSTRING function.

And I don't know what can be causing it. I tried to use SUBSTRING separatedly in another query:

select X = SUBSTRING('PT3M44S',2,1)

And it works fine. This will probably mean that the error comes from the variable @counter, but I don't know why.

Any orientation will be welcome.

Thanks! I've tried to be as much specific as I can. If you think I haven't, please be kind and tell my why!

Ignacio

Answer 1

You need to rewrite your logic.

I searched for @posLetraReciente in your code and found this: SET @posLetraReciente = @counter

And this expression @counter - (@posLetraReciente + 1) always gives you -1.

And you pass this -1 as the third parameter in your SUBSTRING that is invalid

Answer 2

The problem should be in this part of the code:

SUBSTRING(@texto, (@posLetraReciente + 1), @counter - (@posLetraReciente + 1))

If SET @counter = 1 and SET @posLetraReciente = @counter then @counter - (@posLetraReciente + 1) always give you -1

As it says on the documentation:

the third parameter length is a positive integer or bigint expression that specifies how many characters of the expression will be returned. If length is negative, an error is generated and the statement is terminated. If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.