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

[barrytallis]

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?

[Viraj]

Hey, 

Try to create custom column for your table and use following formula.

Source: Sisense Mathematical functions 

TOBIGINT(string)

Converts a string representing a valid BigInt value to BigInt type.

TODOUBLE(string)

Converts a string representing a valid double value to Double type.

TOINT(string)

Converts a string representing a valid Int value to Int type.

[barrytallis]

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]

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.

[javierecfpn · Accepted solution]

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