Forum Discussion

barrytallis's avatar
barrytallis
Data Storage
10-29-2021
Solved

Is there any way to convert a string to a number?

We have a field that has text and "text numbers" .  Any way in a widget to be able to convert the text to a number?
Linux
On cloud
  • javierecfpn's avatar
    javierecfpn
    11-03-2021

    You can have a custom table that converts the field to number, the values that are not possible to be converted are just gonna be empty (null)

    select
    todouble(quantity)
    from (
    select 'AA1' as quantity
    union all select '29228'
    union all select '0001'
    union all select 'xxa$%#1'
    ) dt

barrytallis's avatar
barrytallis
Data Storage
10-29-2021

The problem is that in this column there are also text values (poorly normalized table and poorly structured but it is what it is).  Could i create a custom column and will it build with the TODOUBLE(string) function even though there may be text that doesn't convert?

Viraj's avatar
Viraj
Data Storage
10-29-2021

Then you can use SUBSTRING to extract number from string.

Formula: SUBSTRING(string,index,length)

Returns the substring of a specified length, starting at a specified index.

sample :

Input string is : ABC123

Formula: TOINT(SUBSTRING('ABC123',3,3) 

Output: 123

 

No, you cannot convert 'ABC123' to Double or Integer.