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

This entry participates in the TechNet Guru contributions for June contest.


See also :
http://social.technet.microsoft.com/wiki/contents/articles/17785.sql-server-query-language-transact-sql.aspx
Leave a Comment
  • Please add 8 and 6 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Naomi  N edited Revision 6. Comment: Added link to a thread

  • Naomi  N edited Revision 5. Comment: Changed casing in the title

  • Naomi  N edited Revision 4. Comment: Added link to Guru contributions

  • Peter Geelen - MSFT edited Revision 2. Comment: Fixed color codes to names

Page 1 of 1 (4 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Naomi  N edited Original. Comment: Grammar

  • Peter Geelen - MSFT edited Revision 2. Comment: Fixed color codes to names

  • Naomi  N edited Revision 4. Comment: Added link to Guru contributions

  • Naomi  N edited Revision 5. Comment: Changed casing in the title

  • Naomi  N edited Revision 6. Comment: Added link to a thread

  • 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

Page 1 of 1 (6 items)