sqlinvantive-sql

How to convert text to numbers with Invantive SQL?

I am using the Invantive Query Tool. What is the right way to convert text to numbers?

I get a text field from my database, constructed in a format like 3,4. When I use a calculation function in my query for example 2 * 3,4 it shows 68.

What is the right function to get the right data?

Solution

  • There isn't a way to do that now with the existing functions, so I would suggest a workaround. First, you convert the text to a format Invantive SQL understands (US English), and then convert that new text to a number.

    select to_number(replace('3,4', ',', '.'))