Wikis - Page Details

First published by SathyanarrayananS (8Microsoft Community Contributo)
When: 6 Jun 2013 10:05 AM
Last revision by SathyanarrayananS (8Microsoft Community Contributo)
When: 8 Sep 2013 4:09 AM
Revisions: 9
Comments: 6

Options

Can You Improve This Article?

Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.

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