How To Remove Leading Zeros

Hi
I need to remove leading zeros from a text string of numbers. For example:-
'000012' needs to be '12'
'000123' needs to be '123'
'012345' needs to be '12345'
'008000' needs to be '8000'
'01203' needs to be '1203'
There can be between 1 and 4 zeros at the front of the string. Removing all
zeros is not difficult using String Functions, but I only need to remove the
leading zeros. Does anyone have any suggestions?
Thanksif there are no blanks in the original string, then try this:
select replace(ltrim(replace('001100120','0',' ')),' ','0')
BTW "text string of numbers" might be a database design flaw|||Here is a brute force method.
select replace(ltrim(replace('000012','0',' ')),' ','0')
select replace(ltrim(replace('000123','0',' ')),' ','0')
select replace(ltrim(replace('012345','0',' ')),' ','0')
select replace(ltrim(replace('008000','0',' ')),' ','0')
select replace(ltrim(replace('01203','0',' ')),' ','0')
Payson
andrew wrote:
> Hi
> I need to remove leading zeros from a text string of numbers. For example
:-
> '000012' needs to be '12'
> '000123' needs to be '123'
> '012345' needs to be '12345'
> '008000' needs to be '8000'
> '01203' needs to be '1203'
> There can be between 1 and 4 zeros at the front of the string. Removing a
ll
> zeros is not difficult using String Functions, but I only need to remove t
he
> leading zeros. Does anyone have any suggestions?
> Thanks|||Convert to int (if all the fields are numbers only of course)
declare @.value varchar(20)
select @.value ='000012'
select convert(int,@.value)
http://sqlservercode.blogspot.com/|||convert to int and back to varchar
e.g.
declare @.x table (col1 varchar(6))
insert into @.x
select '000012' union all
select '000123' union all
select '012345' union all
select '008000' union all
select '01203'
select convert(varchar, convert(int, col1)) as Stripped
from @.x
andrew wrote:
> Hi
> I need to remove leading zeros from a text string of numbers. For example
:-
> '000012' needs to be '12'
> '000123' needs to be '123'
> '012345' needs to be '12345'
> '008000' needs to be '8000'
> '01203' needs to be '1203'
> There can be between 1 and 4 zeros at the front of the string. Removing a
ll
> zeros is not difficult using String Functions, but I only need to remove t
he
> leading zeros. Does anyone have any suggestions?
> Thanks|||Thanks guys, some good ideas here - I'll try them out in the morning.
You may be right, Alex, about the design flaw, but it's not my database!
"Trey Walpole" wrote:

> convert to int and back to varchar
> e.g.
> declare @.x table (col1 varchar(6))
> insert into @.x
> select '000012' union all
> select '000123' union all
> select '012345' union all
> select '008000' union all
> select '01203'
> select convert(varchar, convert(int, col1)) as Stripped
> from @.x
>
> andrew wrote:
>|||Once again, thanks for the suggestions, guys. Just for the record,
converting to Integer data type seems the easiest way to go:
SELECT CAST(field_name AS int) AS alias FROM table_name
"andrew" wrote:
> Thanks guys, some good ideas here - I'll try them out in the morning.
> You may be right, Alex, about the design flaw, but it's not my database!
>
> "Trey Walpole" wrote:
>