Finding the index of the first non zero character in a string

[#1]

Hi,

I have a [Serial Number] field which can appear like the following : 000000000000002216

I want to make it 2216 but the amount of zero characters (from the left) can vary from one Serial Number to another.

So, I would like to find a way to retrieve the index of the first non zero character so that I will be able to use the Mid function.

I already tried : Mid ([Serial Number] , '<>0') but it didn't work.

Thank you (:

[agomes1971]

Hi,

please see this

How To Remove Leading Zeros in a Number | Qlik Community

How I can remove leading zeroes

HTH

André Gomes

[sunny_talwar]

May be try this:

[Serial Number] * 1 as [Serial Number]

[Peter_Cammaert]

Check out the almost-never-used function FindOneOf().

Details can be found here: FindOneOf ‒ QlikView

[Peter_Cammaert]

Be careful with one of the "capacity limits" of QlikView.. "Numbers" with more than 14 (or is it 15) digits will not be treated as numbers but will remain text-only.

[sunny_talwar]

Even when multiplied by 1? 000000000000001 * 1 won't end up as 1?

[miguelbraga]

Maybe a tweek a bit like this:

In the script:

(num([Serial Number]) * 1) as [Serial Number]

=num(00000000000000000000000000000001) * 1

Best regards,

D.A. MB

[sunny_talwar]

I think we don't really need to add the Num function, unless you really want to add it. I think * 1 convert the text into a number and will get rid of 0s at the start. But Peter mentioned something interesting and I am not sure if that impacts the multiplication with 1. Waiting for his response to come through

[miguelbraga]

I've put the num just in case the length of the characters is to high like my example above and to be sure that all the values are converted to number value

[sunny_talwar]

If Num() is used for converting it to number, then why do you need * 1 for?