cancel
Showing results for 
Search instead for 
Did you mean: 

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

barrytallis
7 - Data Storage
7 - Data Storage

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?

1 ACCEPTED SOLUTION

javierecfpn
8 - Cloud Apps
8 - Cloud Apps

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

View solution in original post

4 REPLIES 4

Viraj
7 - Data Storage
7 - Data Storage

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.

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?

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
8 - Cloud Apps
8 - Cloud Apps

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

View solution in original post