Convert Values Without Errors Using Safe Casting

Convert strings to numbers (or other types) while returning `NULL` for values that cannot be converted, instead of raising an error that aborts the query.

Created Apr 26, 2026 Last updated Apr 26, 2026 0/4 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Extract numeric amounts from a column that contains non-numeric placeholders

Rows 1, 3, and 5 contain valid integer strings ('150', '200', '75') and are converted to integers. Rows 2 and 4 contain 'N/A' and 'error', which fail the numeric check: SQL Server's TRY_CAST catches the conversion error and returns NULL; the CASE WHEN ... REGEXP and CASE WHEN ... ~ approaches skip the CAST entirely for non-matching strings, defaulting the CASE to NULL. The regex ^[0-9]+$ matches only strings made entirely of digits, which handles the non-negative integer case. For values that could be negative or decimal, the regex would need to be adjusted to ^-?[0-9]+(\.[0-9]+)?$. SQL Server's TRY_CAST handles all numeric formats automatically without a regex.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE raw_data (id INT, amount_text VARCHAR(20));

INSERT INTO
  raw_data
VALUES
  (1, '150'),
  (2, 'N/A'),
  (3, '200'),
  (4, 'error'),
  (5, '75');

Validated query

SQL

SELECT
  id,
  amount_text,
  CASE
    WHEN amount_text REGEXP '^[0-9]+$' THEN CAST(amount_text AS SIGNED)
    ELSE NULL
  END AS amount
FROM
  raw_data
ORDER BY
  id;

Expected result

id	amount_text	amount
1	150	150
2	N/A	NULL
3	200	200
4	error	NULL
5	75	75

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE raw_data (id INT, amount_text VARCHAR(20));

INSERT INTO
  raw_data
VALUES
  (1, '150'),
  (2, 'N/A'),
  (3, '200'),
  (4, 'error'),
  (5, '75');

Validated query

SQL

SELECT
  id,
  amount_text,
  TRY_CAST (amount_text AS INT) AS amount
FROM
  raw_data
ORDER BY
  id;

Expected result

id	amount_text	amount
1	150	150
2	N/A	NULL
3	200	200
4	error	NULL
5	75	75

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE raw_data (id INT, amount_text VARCHAR(20));

INSERT INTO
  raw_data
VALUES
  (1, '150'),
  (2, 'N/A'),
  (3, '200'),
  (4, 'error'),
  (5, '75');

Validated query

SQL

SELECT id, amount_text, CASE WHEN amount_text ~ '^[0-9]+$' THEN amount_text::INT END AS amount FROM raw_data ORDER BY id;

Expected result

id	amount_text	amount
1	150	150
2	N/A	NULL
3	200	200
4	error	NULL
5	75	75

SQL Server uses TRY_CAST natively. PostgreSQL uses a regex-guarded CASE expression with :: cast syntax. MySQL and MariaDB use REGEXP in a CASE expression with CAST AS SIGNED. All return NULL for non-numeric values.

Useful when

Where this command helps.

reading an amount column from a CSV import where some rows contain 'N/A' or empty strings instead of numbers
querying a loosely typed varchar column that is sometimes numeric and sometimes a status code

Explanation

What the command is doing.

A regular CAST('abc' AS INT) raises an error when the value is not a valid number. SQL Server provides TRY_CAST(value AS type) which catches that error and returns NULL, letting the query continue and treating bad values as missing data. TRY_CONVERT(type, value) is the equivalent with argument order reversed and an optional style parameter. PostgreSQL has no built-in safe-cast function but achieves the same result with a CASE WHEN value ~ '^-?[0-9]+$' THEN value::INT END guard expression. MySQL and MariaDB use REGEXP in a CASE expression. SQLite's CAST is already lenient for most types but returns 0 instead of NULL for non-numeric strings cast to integers, so a CASE guard is still required for strict NULL behavior. Safe casting is most useful when loading data from external sources where some rows may contain placeholder text in otherwise-numeric columns.

Convert Values Without Errors Using Safe Casting

Extract numeric amounts from a column that contains non-numeric placeholders

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.