Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Remove Zeros 3

Status
Not open for further replies.

NewToCR

Programmer
Oct 20, 2005
54
US
Hi again,
I have records with 7 and 8 zeros out of 12 characters.
Zeros are preceedeing always. I need to remove those.
Should I use Left function?
 
I am thinking of logic...if it is 7 - remove and if it is 8 - remove. I am confused. Please, help.
 
Replace({your.data}, "0", "") would turn 0000000abc into abd. Or 0000xyz000 into xyz. Is that OK?

If you want to keep non-leading zeros - e.g. 000000001203 - and it is always 7 or 8, then
Code:
if Mid({your.field, 8, 1) = 0 then Right({your.field, 4) else Right({your.field, 5)
This would yield 1203, while 000000062130 would yield 62130.

If it's just a number it would be easier to use editing to suppress leading zeros. Right-click and format field.

[yinyang] Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10 [yinyang]
 
Hi,
Another approach would be to convert the data into a number then back to text..Should eliminate the leading 0s..

ToText(ToNumber('00000002345'),'#') returns 2345

Subsitute your field, of course..

ToText(ToNumber({table.field}),'#')



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Exactly! Thanks a million...but there is something funky with brackets.
 
I got it, Madawc
just for future if someone searching for this

if Mid({your.field}, 8, 1) = 0 then Right({your.field}, 4) else Right({your.field}, 5)

THANKS A LOT

Turkbear,
I will look into ToText too, never got it though:)
 
I am back. My fromula failing me.
Here is what records I have
000000030003 will give me 030003 instead of 30003
000000010010 will give me 010010 instead of 10010
000000123456 this is ok 123456
000000234300 this is ok 234300

I am using
if Mid({mytbl.myfield}, 8, 1) = '0' then Right({mytbl.myfield}, 7) else Right({mytbl.myfield}, 6)

Again, the lenght of the string is always 12 characters.
Some have six lead zeros to remove, some seven to remove.

Thanks
 
Why not just use:

val({table.field})

If you need it to be text, you can use:

totext(val({table.field}),0,"")

-LB
 
I don't see how this will give me 5 digits if there are 7 zeros and 6 digits if 6 zeros. I will try but i don't understand. Thanks
 
It turns the field into a number, so that all leading zeros are removed. The text function just makes it a string without any decimals or comma dividers.

-LB
 
Amazing??? It worked, both! Which one would you preffer?
I like both, can I give more than 1 star?
 
You'll find that you can probably offload this to the database for optimal performance by using a SQL Expression, as with Oracle you might use:

cast(table.field as number)

The database will perform the conversion, and then you'll have the field already in the appropriate format.

You might also consider exposing that table as a View with the conversion already within, this allowing for future flexibility in case your dba figures out that it can be more economically stored as a value because you can just replace it within the View.

-k
 
The latest request was is not to include
val({table.field}) records where ({table.other_field}<>'S')

So everything must be redone...
Now I need to have
select * where ({table.other_field}<>'S')
and then insert val({table.field}) on already filtered data.

Should I insert SQL Expression field or go some other way?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top