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:
>

How to reference a previous field Alias in TSQL as in Jet SQL?

My question is simple, I'd like to do something I do in Jet ANSI-89 SQL. Mind you I'm just adding numbers here - they are not actual columns in 'SomeTable'

SELECT 1 AS A, 2 AS B, A+B AS C
FROM SomeTable

The Jet engine evaluates and does arithmetic on the Aliased column names - handy when they contain their own functions. The resultset would show:

A B C
1 2 3

However from what I can tell SQL Server 2005 is not picking this up. Is their an equivalent?

The Jet syntax is non-standard SQL syntax so it will not work in SQL Server. You will have to use a derived table or CTE (in SQL Server 2005) or use a view or computed column in the table (if the expression involves columns from a single table).

select A, B, A+B as C

from (select 1 as A, 2 as B

from sometable

) as t

with t as (

select 1 as A, 2 as B

from sometable

)

select A, B, A+B as C

from t

How to query a number (street number)...

I have a table that has a street number field.
if the user types in a street number of '2' i would like to return all street numbers the begin with 2 (2,20,21, 200, 201,205,2009,...)
how can this be done.' 200'
' 21'
' 2005'
sorry for confusion just been a long hard day...|||If your street number column is a string, just use the LIKE operator.
If it is not a string, then it should be. Change it.

Rule Of Thumb: If you don't add it, subtract it, multiply it, or divide it, then it is not a number even if it looks like one.|||DECLARE @.MyChar9 CHAR(9)
SET @.MyChar9 =' 20'
SELECT @.MyChar9,LEFT(LTRIM(@.MyChar9),1)
WHERE LTRIM(@.MyChar9) LIKE '2%'


GW|||Correct me if I'm wrong but...
Using LTRIM and LIKE will ignore any indexes.|||Correct me if I'm wrong but...
Using LTRIM and LIKE will ignore any indexes.I cannot because you are not :)

@.OP - simpler just to write
...
@.MyChar9 LIKE ' 2%'
No indexes used there either but less RSI from all that typing.|||... unless of course there might not be leading spaces in which case ignore.|||... unless of course there might not be leading spaces in which case ignore.
In a right justified char(9) column there may be up to 8 leading spaces. Hence Gwilly's LTRIM().|||In a right justified char(9) column there may be up to 8 leading spaces. Hence Gwilly's LTRIM().But there might be none which was why I said ignore my solution (which is likely wrong).|||LIKE will like totally use the index, if the wildcard comes at like the end or whatever. So like this will do an index seek:

where column like 'abcd%'

But this will like totally gag your server:

where column like '%wxyz'

It's like when you wanna text your friend, and you like only know their last name or whatever, you have to go through each one, since the index is ordered by like the first name, you know.

OK. I will go back to English, now ;-)|||' 200'
' 21'
' 2005'
sorry for confusion just been a long hard day...

How is a char(9) field right justified?