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")