Remove irrelevant 0's from a field value

[paulw10]

Hey,
I have a field called externalID with values like the following

1766000000009834
1766000000009835
1766000000009836

and i am looking for a way to remove all the 0's in the middle when i output to a table and then rename the field to something like shortID , so the table output would show the following values

17669834
17669835
17669836

I have tried playing around with functions like eval, ltrim, replace...etc and not getting anywhere.

can anyone help me out.

UPDATE:

Hi,
I need help with this problem again.
So as previously stated i only want to remove the zero's in the middle , but the options given above seem to remove all 0's.

so lets say my external ID is 867182000000921046
i want my table to show 867182921046

but the above options are removing all 0's and giving me 86718292146

any ideas how i can do this.

[paulw10]

Hi,
I need help with this problem again.
So as previously stated i only want to remove the zero's in the middle , but the options given above seem to remove all 0's.

so lets say my external ID is 867182000000921046
i want my table to show 867182921046

but the above options are removing all 0's and giving me 86718292146

any ideas how i can do this.

[richgalloway]

Please see my updated answer.

[paulw10]

Hi,
I need help with this problem again.
So as previously stated i only want to remove the zero's in the middle , but the options given above seem to remove all 0's.

so lets say my external ID is 867182000000921046
i want my table to show 867182921046

but the above options are removing all 0's and giving me 86718292146

any ideas how i can do this.

[paulw10]

Excellent. thank you both

[vnravikumar]

Hi

Try this also

| makeresults 
| eval test=17660000009834 
| rex field=test max_match=0 "(?P<temp>[1-9]+)" 
| eval result = mvjoin(temp,"") 
| table test,result

[richgalloway]

There are at least two ways to do that.

... | eval externalID = replace(externalID, "0{2,}", "")

... | rex field = externalID mode=sed "s/0{2,}//g"