T-SQL: Remove Leading and Trailing Zeros

T-SQL: Remove Leading and Trailing Zeros

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

Sort by: Published Date | Most Recent | Most Useful
Comments
  • Naomi  N edited Original. Comment: Grammar

  • Thank you, it solved my problem,

    Just for somebody if playing with float,

    First convert it to String using STR TSQL function then follow this article

  • Matthew Yarlett edited Revision 8. Comment: Formatted the code block

Page 1 of 1 (3 items)