Remove Leading and Trailing Zeros

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