HOW TO: Remove leading zeros

[disqr_rm]

Found an interesting way to remove leading zeros from a field value. Thought I should share and see if you have a better way to achieve the same results.

Requirement was to remove leading zeros from field values of Material Numnber. This is a text field and can contain numeric or alpha-numeric values.

The trick I could do is:

Material:
load *, replace(ltrim(replace(OldMaterial, '0', ' ')), ' ', 0) as NewMaterial;
LOAD * INLINE [
OldMaterial
000001/230
0000012.34
0120-34.45
];

The result is:

OldMaterial	NewMaterial
0120-34.45	120-34.45
000001/230	1/230
0000012.34	12.34

Do you know a better way to perfor this conversion. Please remember, the field could contain a text value which may have leading zeros, which needs to be removed as well --> just num() doesn't work (even with isnum() function).

I got the results I was looking for but trying to find a better way. Otherwise, this post will serve others in future.

[disqr_rm]

This is certainly a missing functionality (or function) in QV. I could recommend QT to enhance LTRIM function to be capable of taking a character in the parameter and remove that leading character occurences. Same can go with RTRIM and even for TRIM as well.

Somehting like: LTRIM(Text, '0')

[#3]

hello Rakesh.

thank you very much. You saved my lot of time. It was really helpful for me.

Best regards

Muncho

[#4]

Hi rakesh,

its looks good what you do.

Mid(AUFNR, FindOneOf(AUFNR, '123456789AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTyUuVvWwXxYyZz')) as Order_Key
[/n code]
yours seems to be a good one.
Thanks
Sravan</body>

[Anonymous]

Rakesh,

How about this one:

evaluate(OldMaterial & '* 1')

[disqr_rm]

Sure, if your material is numeric. Try with Material 000000000012345ABC.

But I surely liked the trick. Thank you.

[#7]

Hi Rakesh

Once again I have to thank you. I needed an answer to a seemingly simple question, remove leading zero’s. I found it here. Many thanks.

Regards Steve.

[berndjaegle]

Thanks Rakesh, seems to be an old question. Thanks for this post.