Converting int to real in SQLite

sql

data-type-conversion

sqlite-type-affinity

cast-function

byAnton Shumikhin·Sep 25, 2024

Convert an int to real using this SQL statement:

SELECT myIntColumn * 1.0 AS myRealColumn FROM myTable;

This converts myIntColumn to a real by multiplying by 1.0. For precision, you can use CAST function:

SELECT CAST(myIntColumn AS REAL) AS myRealColumn FROM myTable;

It assures that SQLite knows what data type you want to deal with.

Quick conversion method

One quick method to convert int to real involves multiplying the integer by 1.0, effectively forcing SQLite to perform floating-point arithmetic and return a real data type.

SELECT intValue * 1.0 AS realValue FROM tableName;

Understanding SQLite type affinity

SQLite uses a dynamic typing system, called type affinity, where data types can be coerced to other types under certain conditions. When you multiply an int by 1.0, the decimal forces the int into a real, gaining some floaty superpowers in the process.

Precise conversions using CAST

The CAST function is used when high precision is required. It explicitly transforms the data type, signaling SQLite that it should treat the integer as a real.

SELECT CAST(intColumn AS REAL) AS realColumn FROM table;

Hey, data! You've just been promoted to the upper leagues! Enjoy your new powers.

Avoid integer division pitfalls

This might sound crazy, but integer division in SQLite is like cutting a pizza without considering fractions. You'll end up with large pieces and nothing else. Always introduce at least one real to get the tiny fractional pieces (those are the best part, right?).

-- Incorrect: Both numbers are integers. The result is also an integer.
SELECT column1 / column2 AS result FROM table;

-- Correct: By multiplying one of the numbers by 1.0, we get a floating-point result
SELECT column1 * 1.0 / column2 AS result FROM table;

More complex calculations with subqueries and CAST

When dealing with complex calculated fields, utilize CAST within subqueries for both precision and control over data types.

SELECT t1.result * t2.modifier AS finalResult
FROM (
    SELECT CAST(value AS REAL) / total AS result FROM sub_table
) AS t1
JOIN other_table AS t2 ON t1.id = t2.id;