Deleting leading 0's in numbers stored in a text field.

I am trying to use several tables that have one 10-character text field in
common. Most of the records have a numeric expression, but some tables have leading
0's, and some don't.
I can't cast the field to numbers because there are some records that have
letters also.
What function can I use to get rid of all the 0s at the left of each record?
(Sort of a LTRIM function that gets rid of 0s instead of spaces).

Thanks!

While I am not aware of any built-in function to perform this (maybe a good SQLCLR function candidate :) ), this TSQL will work as tested below...

declare @.Field varchar(10)

declare @.i int

set @.Field = '000123405'

set @.i = 1

--remove leading 0s?

if charindex('0', @.Field) = 1

begin

while @.i <= Len(@.Field)

begin

--character a 0?

if charindex('0',@.Field,@.i) = @.i

begin

set @.Field = substring(@.Field, (@.i + 1), (len(@.Field)-@.i))

end

else

begin

break

end

--increment counter

set @.i = @.i + 1

end

end

select @.Field

|||

Leading zeroes are fine for casting character values to numeric/integer/money data types. So it should be fine without doing any trimming. For the rows that have letters you can filter those using a case expression like:

case when col not like '%[^0-9]%' then cast(col as int) end

or below although this checks for conversions to integer/numeric/money data types

case when isnumeric(col) = 1 then cast(col as int) end

And if you want to strp the leading zeroes you can use the expression below:

substring(col, patindex('%[123456789]%', col), 8000 /* 4000 if col is Unicode */)

|||the code above has a bug.. here is the correct and much efficient code

DECLARE @.i INT
,@.output VARCHAR(MAX)
,@.Input varchar(max)

set @.Input = '0012321'

SET @.i = 1

IF CHARINDEX('0', @.Input) = 1
BEGIN
WHILE @.i <= LEN(@.Input)
BEGIN

IF CHARINDEX('0',@.Input,@.i) = 0
BEGIN
SET @.output = SUBSTRING(@.Input,@.i,LEN(@.Input))
BREAK
END

SET @.i = @.i + 1

END

END

RETURN @.output