Remove Leading Zeros

[thingssocomplex]

I need to remove leading zeros from a file that is exported and imported in to another application, the field must be text, I have used the below SQL but it is not removing some of the leading zeros, can somebody please help. For information the length of the code is always 7 digits and it can be one or more leading zeros for example

0012345
0123456

UPDATE TableOne.FieldOne = Replace(LTrim(Replace([TableOne].[FieldOne],"0"," "))," ","0");

 

[apr pillai]

=Replace(LTrim(Replace([TableOne].[FieldOne],"0"," "))," ","0")

You need only the following expression to remove the left side zeroes:

= Val([TableOne].[FieldOne])

 

[Beetle]

Well, your Update query is incorrectly written, but maybe that's just a typo in your post. It should be;

Update TableOne Set FieldOne = Replace(etc.,etc....)

Your conversion should work as it's written, but if you're updating the table before exporting it, then it would be simpler just to type cast it as a number during the update. This is not going to change the underlying data type, so it should still export as text;

Update TableOne Set FieldOne = CLng(FieldOne)

 

[Galaxiom]

Updating to CLng isn't ideal as it would be better to export a query in a single step. (BTW CLng will also fail if the number is greater than 2^31.)

The simplest most reliable expression by far is:
Format(fieldname,"#")

It returns text.

 

[mjseim]

I used this modification and it seems to work and solve for some problems with the previous logic. This modification accounts for IDs that may or may not contain letters.


Explanation:

1. Add a leading space ID. This coerces the ID to a string if it isn’t already.
2. Replace all zeroes with spaces.
3. Perform a LTRIM$ on the string so that all leading zeroes, now spaces, are removed.
4. Replace all spaces with zeroes.

Replace(LTrim$(Replace(" " & [UniqueID],"0"," "))," ","0")

 

[Pat Hartman]

Isn't Replace() going to replace all the zeros, not just the leading zeros? Either CLng() or Format() are better choices.

Leading zeros shouldn't cause the target application a problem. If it treats the value as a string, you want the leading zeros because they have meaning If it treats the value as an integer, the leading zeros will automatically be dropped as the field is imported.

Is the export file fixed width? Stripping the leading zeros means that you will need to left justify the field and substitute trailing spaces to pad the width.