Remove Leading Zeros from Field while loading

[#1]

Hello !

We have a field that is a numerical value with many digits.

AFAIK QV supports up to 11 or 12 digits for a field to be considered numeric.

I wanna load this field as String (since it can't be numeric cause it has too many digits) but REMOVE LEADING ZEROS while loading it.

This is a sample of the field values :

NUMCLI_COCTI ;
000000009279340;
000000008000040;
000000009424789;
000000009401191;
000000009372228;
000000009372228;
000000009021023;
000000009279340;
000000009221292;
000000009424789;
000000009401191;
000031230777806;
000599744000174;
[/CODE]
Is there a LTRIM ,with Zeroes instead of Spaces ? Kind of a RemLeft (Remove Left chars) ...
Any suggestions ?
Thanks !</body>

[sathishkumar_go]

Hi

you want to use as string then use text function.

if want to remove all zero's are 1st 5 places????

because i noted some fileds r 1st 3 places only 0

-Sathish

[#3]

I dont know how many Trailing Zeroes we can have. The sample I provided has a coincidence. But it's not like that...

[sathishkumar_go]

then u want to remove all zeors

ex:

0003000950 u want 395 or how u want?

-Sathish

[#5]

0003000950 I want 3000950

0000000001 I want 1

0000000012 then I want 12

123456789012345 I want 123456789012345

and so forth !

It's simple : REMOVE LEADING ZEROES FROM A FIELD !

[SunilChauhan · Accepted solution]

HI TRY THIS

HOPE THIS WILL WORK

REPLACE

(LTRIM(REPLACE(NUMCLI_COCTI, '0', ' ')), ' ', '0')

GOOD LUCK

[#7]

Hi Adriano

assuming your large number string has a fixed length you could split the field into subparts and create the numeric representation for each of the parts. The recombine the number, multiplying the higher parts with the according number.

You will still hit a border for the highest number representable.

num1 = num(left(NUMCLI_COCTI,8))
num2= num(right(NUMCLI_COCTI,8))
num = (num1 * 100000000) + num2

Regards

Jürg

[bullish35]

Hello. I can remove leading zeroes using this in the script, but second column (containing the non-zero value) is created in the table. Do you know how can I change the value within the existing column? Thank you.

[johnw]

replace(ltrim(replace(YourField,'0',' ')),' ','0') as YourField

[Anonymous]

ltrim(replace(Individual_Item_Number,'77','')) as Dupe So I used this variant to try and identify individual_items where they start with '77%' and return it's non 77 equivalent: Where ind_item = 12345, Dupe is 12345 as it should be. where ind_item = 7712345, Dupe is 12345 as it should be...however, if ind_item = 1277345, it returns 12345 when I would like it to remain 1277345. I only want it to act if the two left most digits meet the criteria.  Thoughts?