DECLARE @tblAccount TABLE (AccountNumber VARCAHR(15))

INSERT INTO @tblAccount SELECT '01010'
INSERT INTO @tblAccount SELECT '0010200'
INSERT INTO @tblAccount SELECT '000103000'

-- Remove leading zeroes
SELECT REPLACE(LTRIM(REPLACE(AccountNumber, '0', ' ')), ' ', '0') AS AccountNumber 
FROM @tblAccount 

-- Remove both leading and trailing zeroes
SELECT REPLACE(RTRIM(LTRIM(REPLACE(AccountNumber, '0', ' '))), ' ', '0') AS AccountNumber 
FROM @tblAccount