Removing leading zeros 2

[Danster]

Gday all,
I have a SQL field Varchar 20 chars long. It almost always contains numeric data, often with leading zeros such as 00001234. How do I get rid of the leading zeros so I just get 1234.

cheers

Danster

 

[lbass]

If the field begins with numeric data, then the following should work:

val({table.field})

Then use format field to remove commas and decimals.

-LB

 

[Ngolem]

It almost always contains numeric data,

Sort of like being a little pregnant isn't it?

I don't think lbass's approach will work since it would choke on the times when the fields were not numeric.

You should treat this as though all were non-numeric

I would do it this way.

//@stripzeros

whilePrintingRecords

stringVar result := trim({Table.fieldwithzeros});
NumberVar pointer;

for pointer := 1 to length(result} do
(
if result[pointer] <> &quot;0&quot; then
exit do;
);

result := right(result, length(result)-pointer);

result;

Jim Broadbent

 

[crystalreporting]

Or a more easily understandable formula could be;

if NumericText({Table.Field})
then
ToText(ToNumber({Table.Field}),0,&quot;&quot
else
{Table.Field}

This checks a field to determine if the value is numeric - if it is it converts to a number - the 0 and &quot;&quot; tells Crystal 0 decimal places, with no thousands separator. In either case the numeric value is converted to text so that you will see the numeric results without leading zeros, and the text results as text - phew!

 

[Danster]

Thanks Crystalreporting,

I liked all suggestions, but tried yours first - mainly because I had done this before but just couldn't remember what I had done. I could only recall it was done with some combination of ToText and ToNumber.
Anyway, it works!
Much appreciated.