Fix “Conversion Error: Could Not Convert String” in SQL

The symptom

You turn a text column into a number, a date, or a true/false value, and one row holds something that isn't one: 'N/A', an empty string, '$12.00', '1,250'. DuckDB stops the whole query at the first value it can't convert. It doesn't skip the row or return a NULL.

The bench below is a stock count typed into a spreadsheet, the usual source: two good numbers, a placeholder, and a blank cell.

Read the message

Could not convert string 'N/A' to INT32 when casting from source column counted has three parts, and each one is a clue:

• The value, 'N/A': the first one DuckDB reached that wouldn't convert. There may be more.
• The target type, in DuckDB's internal name. The table below translates it.
• The column, after source column. It's left off when the value is a literal you typed, such as cast('N/A' as integer).

The decimal version quotes the value in double quotes instead: Could not convert string "N/A" to DECIMAL(10,2). Same error, same fixes.

The fix

try_cast returns NULL for a value it can't convert instead of failing, so the query finishes and the bad rows show up as gaps:

create or replace table stock_counts as
select * from (values
  ('Fiddle-Leaf Fig', '14'),
  ('Monstera Deliciosa', '40'),
  ('Snake Plant', 'N/A'),
  ('ZZ Plant', '')
) as t(product_name, counted);
select product_name, cast(counted as integer) as quantity
from stock_counts;

create or replace table stock_counts as
select * from (values
  ('Fiddle-Leaf Fig', '14'),
  ('Monstera Deliciosa', '40'),
  ('Snake Plant', 'N/A'),
  ('ZZ Plant', '')
) as t(product_name, counted);
select product_name, try_cast(counted as integer) as quantity
from stock_counts
order by product_name;

The Snake Plant and the ZZ Plant come back with a NULL quantity. That keeps the query running, but it hides the problem as well as solving it, so look at what failed before you trust a total.

Find every value that won't convert

The message only shows the first bad value. To list all of them, keep the rows where the text is present but try_cast gives up:

create or replace table stock_counts as
select * from (values
  ('Fiddle-Leaf Fig', '14'),
  ('Monstera Deliciosa', '40'),
  ('Snake Plant', 'N/A'),
  ('ZZ Plant', '')
) as t(product_name, counted);

select product_name, counted
from stock_counts
where counted is not null
  and try_cast(counted as integer) is null
order by product_name;

That returns two rows: 'N/A' and the empty string. The is not null matters: a real NULL casts to NULL without an error, so it isn't one of the values to fix.

Clean the text, then cast

Most failures are formatting, not missing data. Strip what the type can't hold, then cast what's left. Spaces at either end are fine: DuckDB trims them itself.

select price_text,
       try_cast(
         replace(replace(price_text, '$', ''), ',', '') as decimal(10,2)
       ) as price
from (values ('32.00'), ('$12.00'), ('1,250.00'), ('N/A')) as t(price_text);

The first three become 32.00, 12.00, and 1250.00. 'N/A' stays NULL, which is right: it was never a price.

When you never wrote CAST

Comparing a text column to a number makes DuckDB convert the column for you, and that conversion fails the same way. Garden Shop's coupon_code holds codes like SPRING10:

select order_id, coupon_code
from orders
where coupon_code = 10;

DuckDB answers Could not convert string 'SPRING10' to INT32 when casting from source column coupon_code. The same happens with in (1, 2), and with a join that matches a text column to a number column. Compare text with text:

select order_id, coupon_code
from orders
where coupon_code = 'SPRING10'
order by order_id;

That returns orders 1, 8, and 19. A > or < comparison fails earlier, with a different message: Cannot compare values of type VARCHAR and type INTEGER_LITERAL - an explicit cast is required.

“to UINT8” means an enum

An enum stores each allowed label as a small number, and the error names that storage type instead of the enum. So Could not convert string 'processing' to UINT8 means 'processing' isn't one of the enum's labels. Ask the enum for its list:

create or replace type order_status as enum ('pending', 'shipped', 'cancelled');

select enum_range(null::order_status) as allowed;

select distinct status
from orders
where status not in (select unnest(enum_range(null::order_status)));

The second query returns processing, the label the enum forgot. Either add it to the type or map the value before you load it. CREATE TYPE covers changing an enum.

Dates fail with their own message

A date in the wrong layout gives a different conversion error: invalid date field format: "03/15/2024", expected format is (YYYY-MM-DD). Casting only reads YYYY-MM-DD. For anything else, describe the layout with strptime:

select strptime('03/15/2024', '%m/%d/%Y')::date as order_date;

try_strptime is the forgiving version, returning NULL where the text doesn't match the format.

How other engines word it

SQLite and MySQL's CAST don't raise an error at all: they turn a bad value into 0. That's worse than an error, because a total built on those zeros looks believable.

Related errors

• Fix “CHECK Constraint Failed” in SQL

  A value is storable but breaks a business rule the table enforces. Read the expression.

• Fix “NOT NULL Constraint Failed” in SQL

  A required column got no value. Supply one, give the column a default, or let it be null.

• Fix “Syntax Error at or Near” in SQL

  The parser stopped at the first word it could not use. The quoted token is where it gave up, not always where the mistake is.

Learn more

• SQL error messages explained
• Mistake · Why numbers stored as text sort wrong
• Reference · CREATE TYPE and enums
• Lesson 2.1 · Why data types matter
• Lesson 18.2 · Check data quality with SQL