- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to remove the leading zero's in the alphanumeric field values
The data looks like below one of my reporting field
2412HJk0067
0161R11U2
0000000000000002326
0000000000000005338
973-GV-79
0000000000000008349
834-PV-62
0164DU636
00000000000000078517
1437/392
##2WQR4567
-0089WSE9
-08213R8756
-00006543298
Database the field has data type character and values are not maintained the same length .
Data extraction it is extracted as
text(field1) as field1
I would like to remove leading 0 atleast for the values like 0000000000000007851 to 7851.
I tried Num,num#,trim ,left- nothing is working.
Advance thanks for your help.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
mid([GLPCA.RACCT],index([GLPCA.RACCT],left(PurgeChar([GLPCA.RACCT] , '0' ),1)))
[ /code ]
I use this as my standard for mixed numbers and to remove leading zeros
Jim</body>
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
mid([GLPCA.RACCT],index([GLPCA.RACCT],left(PurgeChar([GLPCA.RACCT] , '0' ),1)))
[ /code ]
I use this as my standard for mixed numbers and to remove leading zeros
Jim</body>
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I use this as a standard with no problems:
mid([GLPCA.RACCT],index([GLPCA.RACCT],left(PurgeChar([GLPCA.RACCT] , '0' ),1)))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your solution .
It is working
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
seems to me like a bug. Have experimented and apparently QV interprets numbers with more than
14 leading "0"s always as a text, with lesser "0"s it would interpret a number like 0000000000000078517 as 78517.
You might use the Replace-function:
Replace(YourField, '00000000000000', '') AS TextOrNumber
but this will fail for e.g. 00000000000000078517A as it would display then 78517A and no longer the full string, which is probably needed.
HTH
Peter
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This is why I recommend using replace with trim combination. Otherwise you will have problem with "long" numbers.
replace(ltrim(replace(OldMaterial, '0', ' ')), ' ', 0) as NewMaterial
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Rakesh,
our messages crossed - pretty nice solution. Though depending on the meaning of the data it might be of importance whether you have 0000456A or 456A ......
Peter
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Rakesh,
Thanks for the example. It works great.
Thom