Algorithms to trim leading zeroes from a SQL field?

Question

I just came across the interesting problem of trying to trim the leading zeroes from a non-numeric field in SQL. (Since it can contain characters, it can't just be converted to a number and then back.)

This is what we ended up using:

SELECT REPLACE(LTRIM(REPLACE(fieldWithLeadingZeroes,'0',' ')),' ','0')

It replaces the zeroes with spaces, left trims it, and then puts the zeroes back in. I thought this was a very clever and interesting way to do it, although not so readable if you've never come across it before.

Are there any clearer ways to do this? Any more efficient ways to do this? Or any other ways to do this period? I was intrigued by this problem and would be interested to see any methods of getting around it.

Answer 1

Check out TRIM function http://www.1keydata.com/sql/sql-trim.html

SELECT TRIM(LEADING '0' FROM fieldWithLeadingZeroes)

Answer 2

Find 1st non-zero character, substring to end (can put 2 billion for max types)

LTRIM is optional, really

DECLARE @MyVar varchar(8000)

SET @MyVar = '000foobar'
SELECT LTRIM(SUBSTRING(@MyVar, PATINDEX ('%[^0]%', @MyVar), 8000))
SET @MyVar = '000000  oh my lord'
SELECT LTRIM(SUBSTRING(@MyVar, PATINDEX ('%[^0]%', @MyVar), 8000))