SQL Server Function.

Question

What is wrong with this function... The function intends to remove the specified leading characters from a given string. I know there is a patindex base solution to this which however doesn't consider spaces and all zero entries... but I want to know what is wrong with this one...

If I input "00012345" it should out put me "12345" however the output I'm getting is "0001234".. Why?

The test data is:

DECLARE @result varchar(max)
EXEC @result = TrimLeadingChar '00012345'
PRINT @result

The function code is:

CREATE FUNCTION TrimLeadingChar
(
    @st AS Varchar(max),
    @trimChar AS Varchar(1) = "0"
)
RETURNS Varchar(max)
AS
BEGIN
    DECLARE @index int
    DECLARE @temp Varchar(1)
    SET @index = 0
    if LEN(RTRIM(LTRIM(@st))) <= 1
     return @st;
    While(@index < LEN(@st))
    BEGIN
     set @temp = substring(@st,@index,1)        
     if @temp = @trimChar
      SET @index = @index + 1
     else
      Break;                   
    END
    Return substring(@st,@index, LEN(@st))
END
GO

Answer 1

set @temp = substring(@st,@index+1,1)

instead of

set @temp = substring(@st,@index,1)

UPDATE:

OR you should set @index = 1 at first

DECLARE @index int
DECLARE @temp Varchar(1)
SET @index = 1

then

set @temp = substring(@st,@index,1)

Answer 2

Just for the sake of other users: Here is the working and complete solution function for SQL Server:

CREATE FUNCTION TrimBothEndsAndRemoveLeadingChar
(
    @st AS VARCHAR(MAX),
    @trimChar AS VARCHAR(1) = "0"
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @index INT
    DECLARE @temp VARCHAR(1)

    IF @st IS NULL OR @trimChar IS NULL OR LEN(RTRIM(LTRIM(@st))) <= 0
        RETURN @st

    SET @st = LTRIM(RTRIM(@st))    
    SET @index = 1    
    WHILE(@index <= LEN(@st))
    BEGIN
     SET @temp = SUBSTRING(@st, @index, 1)        
     IF @temp = @trimChar
      SET @index = @index + 1
     ELSE
      BREAK;                   
    END

    DECLARE @result VARCHAR(MAX)
    IF @index = (LEN(@st) + 1)
        SET @result = @st
    ELSE
        SET @result = SUBSTRING(@st, @index, LEN(@st) + 1)
    RETURN @result
END
GO