In this post I have consolidated few of the methods to remove leading and trailing zeros in a string .

Here is an example :

DECLARE @BankAccount TABLE (AccNo VARCHAR(15))
INSERT @BankAccount SELECT '01010'
INSERT @BankAccount SELECT '0010200'
INSERT @BankAccount SELECT '000103000'
  
SELECT * FROM @BankAccount
  
--Methods to remove leading zeros
  
-- 1.)  converting to integer data type
SELECT CONVERT(INT,AccNo) AccNo FROM @BankAccount 

-- NN - note, this method will only work if the data are clean
  
-- 2.)  using SUBSTRING 
  
SELECT SUBSTRING(AccNo,PATINDEX('%[^0]%',AccNo),LEN(AccNo)) AccNo FROM @BankAccount
  
-- 3.)  using REPLACE,LTRIM & RTRIM
  
SELECT REPLACE(LTRIM(REPLACE(AccNo,'0',' ')),' ','0') AccNo FROM @BankAccount
 
--To remove both leading & trailing zeros
  
SELECT REPLACE(RTRIM(LTRIM(REPLACE(AccNo,'0',' '))),' ','0') AccNo FROM @BankAccount


See Also