ClickHouse DB::Exception: Cannot parse number from string

The "DB::Exception: Cannot parse number from string" error occurs when ClickHouse tries to convert a text value into a numeric type and fails. The CANNOT_PARSE_NUMBER error code covers all numeric types -- Int8 through Int256, UInt variants, Float32, Float64, and Decimal types. If the string does not look like a valid number to ClickHouse's parser, this is the error you get.

Impact

This error causes the INSERT or query to fail:

• The entire batch is rejected, so valid rows are lost along with the bad ones.
• It is one of the most frequently encountered errors in data ingestion pipelines because real-world data is messy -- CSV exports from spreadsheets, user-generated content, and third-party feeds often contain non-numeric values in numeric fields.
• Repeated failures can block pipeline progress and cause data lag.

Common Causes

1. Non-numeric placeholder values -- fields containing N/A, NULL, -, n/a, or other sentinel values in columns defined as Int or Float.
2. Thousands separators -- numbers formatted as 1,000,000 instead of 1000000.
3. Currency symbols or units -- values like $99.95 or 100 MB that include non-numeric characters.
4. Empty strings -- an empty CSV field mapped to a non-Nullable numeric column.
5. Decimal comma locales -- numbers formatted as 3,14 (European style) instead of 3.14.
6. Overflow values -- a number that is valid but exceeds the range of the target type (e.g., 300 for an Int8 column).
7. Scientific notation mismatches -- values like 1.5e10 being inserted into an integer column that does not accept floating-point notation.

Troubleshooting and Resolution Steps

1. Read the error message carefully. ClickHouse reports the specific string and column:

   DB::Exception: Cannot parse number 'N/A' for column 'amount' of type Int32
   

2. Make columns Nullable for optional data:

   ALTER TABLE my_table MODIFY COLUMN amount Nullable(Int32);
   

3. Use input_format_csv_empty_as_default for empty fields:

   SET input_format_csv_empty_as_default = 1;
   INSERT INTO my_table FORMAT CSV
   

4. Use safe-cast functions when transforming data in queries:

   SELECT toInt32OrNull(raw_amount) AS amount,
          toFloat64OrZero(raw_price) AS price
   FROM staging_table;
   

5. Preprocess the data to strip non-numeric characters:

   # Remove dollar signs and commas
   sed 's/\$//g; s/,//g' data.csv > data_clean.csv
   

6. Skip bad rows during import with error tolerance:

   SET input_format_allow_errors_num = 100;
   INSERT INTO my_table FORMAT CSV
   

7. Find all non-numeric values in a specific column before importing:

   clickhouse-local --query="SELECT line, col3 FROM file('data.csv', CSV, 'col1 String, col2 String, col3 String') WHERE toInt32OrNull(col3) IS NULL AND col3 != '' LIMIT 20"
   

Best Practices

• Use Nullable types for any numeric column that might receive empty or missing values from external sources.
• Prefer the OrNull and OrZero function families (toInt32OrNull, toFloat64OrZero) in transformation queries to handle edge cases gracefully.
• Strip formatting (currency symbols, thousands separators, units) in your ETL pipeline before data reaches ClickHouse.
• Set input_format_allow_errors_num or input_format_allow_errors_ratio in production pipelines to avoid total failure from a few bad values.
• Document expected numeric formats for each data source and validate them at ingestion time.

Frequently Asked Questions

Q: Can ClickHouse handle numbers with thousands separators like 1,000,000?
A: No, not by default. ClickHouse expects plain numeric strings without formatting. You need to strip commas before inserting, either in your ETL pipeline or using string functions like replaceAll(raw_value, ',', '').

Q: What happens if I insert a float like 3.14 into an Int32 column?
A: ClickHouse will raise an error because it cannot parse the decimal point as part of an integer. Use toInt32(toFloat64('3.14')) to truncate, or change the column to Float64 or Decimal.

Q: How do I handle empty CSV fields for numeric columns?
A: Either make the column Nullable (so empty becomes NULL) or set input_format_csv_empty_as_default = 1 so that empty fields get the column's default value (typically 0 for numeric types).

Q: My data has values like 1.5e10 (scientific notation). Will ClickHouse parse them?
A: Float32 and Float64 columns accept scientific notation. Integer columns do not. If your integer column receives values in scientific notation, you will need to cast through a Float first or preprocess the data.

Q: Can I use toInt32OrDefault to provide a fallback value?
A: Yes. toInt32OrDefault(value, -1) will return -1 for any string that cannot be parsed as an Int32. This is useful in transformation queries but does not apply directly during FORMAT-based inserts.