Remove Leading Zero

[BI_Pleb]

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

123

Null

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

[charlie_archer · Accepted solution]

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

[michael_treadwell · Accepted solution]

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.

[BI_Pleb]

Brilliant, so simple but worked a treat!

Thanks a lot guys

[AngelaO]

Another option is to only Replace with RegEx when there are 2 or more leading zeros.

\<0{2}+

\< Start of word

0 Specific Character

{2]+ two or more times