How to remove the leading zero's in the alphanumeric field values

[#1]

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.

[jbeierschmitt · Accepted solution]

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>

[disqr_rm]

Look here:

http://community.qlik.com/forums/t/17456.aspx

[jbeierschmitt]

I use this as a standard with no problems:

mid([GLPCA.RACCT],index([GLPCA.RACCT],left(PurgeChar([GLPCA.RACCT] , '0' ),1)))

[#5]

Thanks for your solution .

It is working

[prieper]

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

[disqr_rm]

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

[prieper]

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

[tmumaw]

Rakesh,

Thanks for the example.  It works great.

Thom