String to Number with Logic

[Tushar_07]

I ran a workflow on multiple files in a loop and in the result I got a column which has different type of values in it. The table looks like following.
ID Roll No
A 8569
B 8.69678968E9
C 166D785
D 758966
E 9.36365897E9
F 15587B58

From the numbers we can see that some numbers are in Scientific form, some are in integer or double form , and some numbers has text in it. I want the Roll no column in somewhat like Alphanumeric Column. Also I don’t want my Values in Scientific Form.

Desired Result:

ID Roll No
A 8569
B 8696789680
C 166D785
D 758966
E 9363658970
F 15587B58

I tried Java snippet and column expression, but because of the data type for column Roll no is “?”, it failed. Is there any way I can get this result?

Thanks,
Tushar

[armingrudd]

Hi @Tushar_07,

This expression in the Column Expressions node will do what you want: (set the output type to string)

if(regexMatcher(column("column1"), "^\\d\\.\\d+E\\d+")) {
    m = toInt(regexReplace(column("column1"), ".*E", ""))
    toLong(toDouble(regexReplace(column("column1"), "E.*", "")) * pow(10, m))
}

else column("column1")

[Tushar_07]

Hi @armingrudd

Your expression worked perfectly.

Thanks,
Tushar