Convert a number to a string

-- Portable.
SELECT CAST(42 AS VARCHAR);                   -- '42'
SELECT CAST(3.14 AS VARCHAR);                 -- '3.14'

-- Postgres / DuckDB / Snowflake shorthand.
SELECT 42::VARCHAR;
SELECT 3.14::TEXT;

-- Format-aware (Postgres / Oracle / Snowflake / Redshift).
SELECT TO_CHAR(1234.5, '999,999.99');         -- ' 1,234.50'
SELECT TO_CHAR(0.234, '99%');                  -- ' 23%' (* 100 implicit with %)
SELECT TO_CHAR(42, 'FM0009');                  -- '0042' (FM = no padding spaces)

-- DuckDB / SQLite (printf-style).
SELECT printf('%.2f', 3.14);                   -- '3.14'
SELECT printf('%05d', 42);                     -- '00042'

-- MySQL.
SELECT FORMAT(1234.5, 2);                      -- '1,234.50' (locale-aware)
SELECT CONCAT(42);                             -- implicit cast via concat

-- BigQuery.
SELECT FORMAT('%.2f', 3.14);                   -- '3.14' (printf-style)
SELECT FORMAT('%,d', 1234);                    -- '1,234'

-- SQL Server.
SELECT FORMAT(1234.5, 'N2');                   -- '1,234.50'
SELECT CONVERT(VARCHAR, 42);                   -- '42'

-- Format with two decimal places (defensive against FLOAT precision).
SELECT TO_CHAR(ROUND(price::NUMERIC, 2), 'FM999990.00') AS price_str FROM products;
SELECT FORMAT(ROUND(price, 2), 2)                        AS price_str FROM products;  -- MySQL

-- Build an ID with leading zeros.
SELECT 'ORD-' || LPAD(CAST(id AS VARCHAR), 6, '0') AS order_code FROM orders;

-- Display-ready currency string.
SELECT '$' || TO_CHAR(amount, 'FM999,999,999.00') AS display_amount FROM invoices;

-- Concatenate number into a sentence.
SELECT 'Order ' || order_id || ' totals $' || total AS message FROM orders;

-- Convert big numbers to k / M / B for compact display.
SELECT
  CASE
    WHEN total >= 1e9 THEN ROUND(total / 1e9, 1) || 'B'
    WHEN total >= 1e6 THEN ROUND(total / 1e6, 1) || 'M'
    WHEN total >= 1e3 THEN ROUND(total / 1e3, 1) || 'K'
    ELSE total::VARCHAR
  END AS compact
FROM revenue;