Using CTEs in SQLite to generate a square root. sqrt() isn't a native function within SQLite, so I wanted to see if I could recreate the process within a recursive CTE instead. Unfortunately, there doesn't seem to be a way to dynamically alter the initial values in a recursive CTE (at least not in a straightforward way), so this example will onl…

sqrt.sql

WITH RECURSIVE rsqrt AS (
    SELECT  27  AS x,
            1   AS step,
            1   AS g

    UNION ALL

    SELECT  x AS x,
            step+1 AS step,
            CASE WHEN g = ((x/g + g) /2.0) THEN x ELSE ((x/g + g) /2.0) END AS g
    FROM    rsqrt
    WHERE   g <> ((x/g + g) /2.0)
),

sqrt AS (
    SELECT  g
    FROM    rsqrt
    ORDER BY step DESC
    LIMIT   1
)

SELECT g FROM sqrt;