Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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')
hello Rakesh.
thank you very much. You saved my lot of time. It was really helpful for me.
Best regards
Muncho
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>
Rakesh,
How about this one:
evaluate(OldMaterial & '* 1')
Sure, if your material is numeric. Try with Material 000000000012345ABC.
But I surely liked the trick. Thank you.
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.
Thanks Rakesh, seems to be an old question. Thanks for this post.