Remove leading zeros

[innodat]

I'm stuck in my though process...

What's the easiest way to remove any number of leading "0"s from a string?

005602

or

00067800

[LaRetta]

GetAsNumber ( field )

UPDATE: It doesn't matter if your field is text or number. You can use Replace Field Contents[], back up first or looping script. If you want, you can also add it as auto-enter (replace). If changing your data, remember to protect from record locking as well.

Edited February 18, 2010 by Guest
Added update

[mr_vodka]

Well if its only numbers then you can use GetAsNumber or Evaluate.

*Edit - LaRetta beat me to the punch.

Edited February 18, 2010 by Guest

[innodat]

What if it's not only numbers?

A part number could contain

0056SET-M34

Or what if the field contains "Michael"?

PS: Thank's for competing to help me! :-)

[innodat]

I tried

GetAsNumber and Evaluate

in my "Set Field (Calculation)" script step, but it didn't work.

[TheTominator]

This isn't the easiest way, but it is robust against all that you have so far described.

Let(

[

origStringLen = Length(StringWithLeadingZeros);

nonZeroString = Filter(StringWithLeadingZeros; Substitute(StringWithLeadingZeros; "0"; ""));

posFirstNonzero = Position(StringWithLeadingZeros; Left(nonZeroString ;1); 1; 1)

];

Case(

nonZeroString = ""; "";

posFirstNonzero = 0; "";

Middle(StringWithLeadingZeros; posFirstNonZero; origStringLen - posFirstNonZero + 1)

)

)

[innodat]

It works!!!

Amazing, I could have never done that...

THANK YOU

[comment]

I have a small bone to pick here: I am quite sure that GetAsNumber() should work perfectly well here. I didn't think a more complicated formula was necessary. However, you say that it works while GetAsNumber() doesn't - and I'd like to know why.

[mr_vodka]

Hi Michael.

I think you may have missed the one subsequent post where he states that the field has a mixture of numbers, symbols, and text.

What if it's not only numbers?

A part number could contain

0056SET-M34

Or what if the field contains "Michael"?

Edited February 18, 2010 by Guest

[TheTominator]

I have a small bone to pick here: I am quite sure that GetAsNumber() should work perfectly well here. I didn't think a more complicated formula was necessary. However, you say that it works while and GetAsNumber() doesn't - and I'd like to know why.

The original poster asked about removing leading zeroes from a string. While the original post had pure numeric digits in the string, an additional requirement was added in a later post, i.e. it must work for non-numbers such as 0056SET-M34.

GetAsNumber("0056SET-M34") yields "5634" whereas the desired result is "56SET-M34".

Please let me know if you need further clarification.

[comment]

Indeed, I have missed that post. But in such case, the formula can be somewhat simpler:

Let ( [

clean = Substitute ( text ; "0" ; "" ) ;

start = Position ( text ; Left ( clean ; 1 ) ; 1 ; 1 )

] ;

Right ( text ; Length ( text ) - start + 1 )

)

[innodat]

Thanks comment!

PS: whenever I get an email notification about a post of yours, it reads "Carpal Tunnel" under your forum name "comment" - though I let you know in case you're not aware of it.

[TheTominator]

Let ( [

clean = Substitute ( text ; "0" ; "" ) ;

start = Position ( text ; Left ( clean ; 1 ) ; 1 ; 1 )

] ;

Right ( text ; Length ( text ) - start + 1 )

)

For an input of "000" this calculation yields "000".

It is my understanding that all zeros at the beginning of the string are to be removed, but there may be some ambiguity in the requirements. It could be that there will never be an input string that consists solely of zeros and thus this situation will never arise, or that the correct answer is "000" when provide with an input of "000".

[mr_vodka]

HEre is one that I posted earlier that I took down after seeing all the action on the thread. I am going to repost.

Let ( [ x = YourField;

        c = Left ( Substitute ( x; 0; "" ); 1 );

        p = Position ( x; c; 1; 1 )

      ]; Case ( p ; Right ( x; Length ( x ) - p + 1 ) )



    )

[comment]

whenever I get an email notification about a post of yours, it reads "Carpal Tunnel" under your forum name "comment" - though I let you know in case you're not aware of it.

Thanks. There's not much I can do about it - but perhaps Stephen will notice this.

[_henry_]

Hi,

You can take a look at this CF as your reference from Brian dunning website. I used this CF for removing leading zeroes.

Removing Leading Zeroes

For your convenience, I just grabbed directly from the website:

Let([

C1 = PatternCount("0"; Left(text; 1));

Ca = C1 ];

If(Ca;

TrimLZ(Middle(text; 1 + C1; Length(text) - Ca));

Text)

)

/*Inspiration & much code from: Ray Cologon, NightWing Enterprises*/