How to remove beginning zeros / avoid scientific notation?

[ThomasChen]

Hi guys,
I have a column “EQUIPMENT” with values “000000003000393052, 000000003000397538 …”.

I want to remove all the zeros in the beginnings. My solution is changing this column from string to double and then change it from double to string.

However, the values become scientific notation when they become double.

Can you guys teach me how to remove the beginning zeros or teach me how to avoid scientific notation? Thanks!

[HansS]

Hi @ThomasChen

You can use a String Manipulation node and use:

substr($column1$ , indexOfChars($column1$,“123456789” ) )

gr. Hans

[ThomasChen]

Thank you very much!

[takbb]

Hi @ThomasChen, you already have a good solution from @HansS, but as is often the case with KNIME, there can be multiple methods and so some of these may be of use for other future similar use cases, and are in many ways a matter of personal choice.

The String to Number node can convert all the Strings to Long by setting “type” to “Long” in the config, resulting in Long Integers without scientific notation.

The String Manipulation can also convert Strings to Long, so could either return:
toLong($column1$)
or, if you wish to keep the result as a string:
string(toLong($column1$))

String Manipulation could also remove leading zeroes using regexReplace:
regexReplace($column1$,"^[0]*(.*?)","$1")

or alternatively the equivalent regex replacement can be performed in String Replacer:
replace pattern ^[0]*(.*?) with $1
(or replace [0]*(.*?) with $1, specifying “whole string” in the config)

btw, in your original example String to Number followed by Number to String, if in your String to Number node you had chosen “Long” instead of “Double”, it would have worked.

If you do find yourself with a Double that really represents an integer value, and you want to convert it to a String without scientific notation, you can also use the Number Rounder node (formerly in previous KNIME versions, the Round Double node). In Number Rounder you need to set “Rounding to digits” to 0 and then expand the “advanced settings” at the bottom, and set output mode (legacy) to “Plain String”.

All those choices, and doubtless more besides…