Length of a number with leading zeros

[#1]

Hi all,

I have a problem to calculate the length of a numeric field with leading zeros.

My field comes from a MySQL database and has values like these:

Number

000

1

39

001301

I need to calculate the length of Number in the script and I want to count the zeros too.

I want this result:

Number        Length_Number

000                   3

1                      1

39                     2

001301              6

I tried to use  in the script

load *,

len(Number) as Length_Number ;

sql select  Number

from table;

but it results:

Number        Length_Number

000                   1

000                   2

000                   3

000                   4

.......

000                   13

1                      1

39                     2

39                     4

001301              6

001301              8

and so on.

I tried to use

load *;

sql select  Number,

char_length(Number) as Length_Number;

from table;

and I had the same result.

Can someone help me? Thanks.

[yduval75]

Try this

LOAD

  Number,

  Len(Text(Number)) as Length_Number

;

sql select 

  Number

from table;

[Anonymous]

try

load

number,

len(trim(text(Number)) as Lenght_Number

trim to delete leading zeros

[yduval75]

Trim will not delete zeros but spaces

[Anonymous]

thats right, that is if you read only half

[yduval75]

Are you sure ?

[Anonymous]

my mistake, I looked at the result an dthought it migth be different length due to spaces

but my intention was not to delete zeros (but spaces)

but what is curious is that the "000" is repeated so many times

[yduval75]

Indeed, but I think that Cristina doesn't want delete zeros

[MarcoWedel]

Len(Num(Number))

[MarcoWedel]

Or

=Floor(log10(Number))+1