In this Blog, I will discuss about “How to remove Leading Zeros and Spaces from a value in SQL server”. I have written procedure for it . See the below code .
CREATE PROCEDURE [dbo].[RmLeadZerosSpcs] (@String VARCHAR(500) )
as
BEGIN
DECLARE @output VARCHAR(100) = '';
DECLARE @prev CHAR(1) = '';
DECLARE @c CHAR(1) = '';
DECLARE @i int = 1;
DECLARE @len int;
DECLARE @len_zeros int =0;
SET @len = len(@String);
WHILE(@i <= @len) begin SET @c = SUBSTRING(@String, @i, 1);
IF @c='0' or @c=' '
BEGIN
SET @len_zeros=len(@c)+@i
SET @output = substring(@String,@len_zeros, @len);
SET @i = @i + 1;
END ; IF @c<>'0' and @c!=' '
BEGIN
SET @i = @i + 1;
break;
END
END
select @output;
END
Here, If you look at the code, there is variable @c which will always hold one character at each Iteration and “IF” is checking whether its a “0” or a space ” “. If character is “0” or ” “, then apply substring to trim the String from that character position. This will run till it finds a value other than “0” or ” ” and it will exit from the code using break function.