Convert a string to a number

-- Portable.
SELECT CAST('42' AS INTEGER);
SELECT CAST('3.14' AS DECIMAL(10, 4));
SELECT CAST('1.5e3' AS DOUBLE);

-- Postgres / DuckDB / Snowflake / Redshift shorthand.
SELECT '42'::INTEGER;
SELECT '3.14'::NUMERIC;

-- Safe — return NULL on parse failure.
SELECT TRY_CAST('not-a-number' AS INTEGER);            -- DuckDB / SQL Server / Snowflake
SELECT SAFE_CAST('not-a-number' AS INT64);             -- BigQuery
-- Postgres has no native TRY_CAST; use a regex pre-check or a CASE.

-- Format-aware parse.
SELECT TO_NUMBER('1,234.56', '999,999.99');            -- Postgres / Oracle / Snowflake
SELECT CAST(REPLACE('1,234.56', ',', '') AS DECIMAL);  -- portable workaround

-- Pre-clean before cast.
WITH listings(price_text) AS (
  VALUES ('$1,299.00'), ('1 299 kr'), ('-42.50')
)
SELECT CAST(REGEXP_REPLACE(price_text, '[^0-9.\-]', '', 'g') AS DECIMAL(10, 2)) AS price
FROM   listings;

-- Filter only rows where the cast would succeed (Postgres workaround for TRY_CAST).
WITH t(s) AS (VALUES ('42'), ('-7'), ('abc'))
SELECT CAST(s AS INTEGER) FROM t WHERE s ~ '^-?[0-9]+$';

-- Currency parsing.
SELECT CAST(REPLACE(REPLACE('$1,234.56', '$', ''), ',', '') AS DECIMAL(10, 2));

-- Percent parsing.
SELECT CAST(REPLACE('23.4%', '%', '') AS DECIMAL(5, 2)) / 100 AS rate;

-- Boolean-ish strings.
WITH raw_input(s) AS (VALUES ('true'), ('Yes'), ('0'), ('1'))
SELECT CASE LOWER(s)
         WHEN 'true' THEN 1
         WHEN 'yes'  THEN 1
         WHEN '1'    THEN 1
         ELSE 0
       END AS as_bool
FROM   raw_input;