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

-- 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