Share via

T-SQL: Remove Leading and Trailing Zeros

  • Article

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