Remove Zeros 3

[NewToCR]

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?

 

[NewToCR]

I am thinking of logic...if it is 7 - remove and if it is 8 - remove. I am confused. Please, help.

 

[Madawc]

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

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.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Turkbear]

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}),'#')





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[NewToCR]

Exactly! Thanks a million...but there is something funky with brackets.

 

[NewToCR]

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

 

[NewToCR]

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

 

[lbass]

Why not just use:

val({table.field})

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

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

-LB

 

[NewToCR]

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

 

[lbass]

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

 

[NewToCR]

Amazing??? It worked, both! Which one would you preffer?
I like both, can I give more than 1 star?

 

[synapsevampire]

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

 

[NewToCR]

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?

 

[NewToCR]

I figured that out, thanks a lot.