Trimming leading zeros out of a string

[CzechNET]

Hello I have a table like this:

TBL_TABLE
- ACCOUNT_NUMBER

Now the account number is a string which has 000 in front, some have 3 zeroes and some 5, I'm looking for a query that will remove all the leading zeros. For example record that is like this: 0000898669 would end up being: 898669

I came up with a query like this, but it doesn't remove all zeros, only first 2
UPDATE Table SET ACCOUNT_NUMBER = Mid(ACCOUNT_NUMBER,2,19)

Any ideas ?

 

[ChillAPAC]

If your account_numbers are all numeric characters you could use this:
to_number(account_number).
If you needed it to stay in string format you could to_char(to_number(account_number))

If the values aren't always numeric in your string but the first 5 are always numeric you could convert the first 5 characters to number and then back to character and concentacate with characters 5 on.

to_char(to_number(substr(account_number,1,5))) || substr(account_number,5,5)

 

[CzechNET]

Thanks, I have to execute this on Access and to_number is not recognized function by access. Any other ideas ?

 

[PHV]

Use the Val function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CzechNET]

Doesn't quite work either it converts it to somehting like 1.2151651651+E I only need the leading zeros trimed

 

[PHV]

And this ?
Val(Left(ACCOUNT_NUMBER,5)) & Mid(ACCOUNT_NUMBER,6)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dnoeth]

Why are you posting to the ANSI SQL Forum instead of an Access Forum?

Btw, in ANSI SQL it's:
TRIM(LEADING '0' FROM account_number)

Dieter