Remove Leading Zero

I have a data source which has column with mix of numbers and char and would like to strip leading zero from number value

000000123

000123002

ABC

Expected output:

123

123002

ABC

Using ToNumber() is providing the below:

123

Null

I can get round the Null but the 000123002 becoming 123 is a challange...any ideas?

Accepted answers

Hi there,

You will need the TRIMLEFT function to do this.

TRIMLEFT([FieldName],"0") will remove all "0" characters from the beginning of the string.

Using TRIM will remove the string from the beginning and end of the string.

Thanks

Charlie

Note that @charlie_archer's method will work to trim leading zeros but it will also replace a single zero with a blank string.

To get around this you could filter all strings that == '0', use TRIMLEFT() on all other strings, then union the results.

You could also use the Regex tool to find and replace leading zeros.