---
last_updated: "2026-09-22T11:50:48.000Z"
title: CANNOT_PARSE_TEXT ClickHouse® error
meta:
  description: Learn how to fix the CANNOT_PARSE_TEXT error in ClickHouse and Tinybird. Understand what causes it and see examples of how to resolve it
---

# CANNOT_PARSE_TEXT ClickHouse® error

{% callout type="info" %}
This error usually means ClickHouse® can't parse a text value into the expected data type. Check the format of your text data and make sure it matches the expected type.
{% /callout %}

The `CANNOT_PARSE_TEXT` error in ClickHouse® (and Tinybird) happens when you try to convert a text value to a specific data type, but the text format doesn't match what ClickHouse® expects. This commonly occurs when importing data or using type conversion functions.

## What causes this error

You typically see it when:

* Converting text to numeric types with invalid formats
* Parsing date/time strings with wrong formats
* Converting text to boolean values incorrectly
* Using type conversion functions on malformed text
* Importing data with inconsistent text formats

## Example errors

```sql {% title="Fails: invalid number format" %}
SELECT toInt32('not a number')
```

```sql {% title="Fails: invalid number format" %}
SELECT toInt32('not a number')
```

```sql {% title="Fails: invalid integer format" %}
SELECT toInt64('not an integer')
```

```sql {% title="Fails: invalid integer format" %}
SELECT toInt64('not an integer')
```

## How to fix it

### Use proper data formats

Ensure your text data matches the expected format:

```sql {% title="Fix: valid number format" %}
SELECT toInt32('123')
```

```sql {% title="Fix: valid date format" %}
SELECT toDate('2023-01-01')  -- Works with YYYY-MM-DD format
```

```sql {% title="Fix: valid boolean format" %}
SELECT toBool('true')
```

### Use try functions for error handling

Use `try` functions to handle parsing errors gracefully:

```sql {% title="Fix: use try function" %}
SELECT toInt32OrZero(text_column) as parsed_number FROM events
```

### Validate data before conversion

Check data format before conversion:

```sql {% title="Fix: validate before conversion" %}
SELECT CASE
    WHEN text_column REGEXP '^[0-9]+$' THEN toInt32(text_column)
    ELSE NULL
END as parsed_number FROM events
```

## Common patterns and solutions

### Numeric parsing

Common numeric parsing patterns:

```sql {% title="Integer parsing" %}
SELECT toInt32('123')  -- Valid integer
SELECT toInt32OrZero('abc')  -- Returns 0 for invalid
SELECT toInt32OrNull('abc')  -- Returns NULL for invalid
```

```sql {% title="Float parsing" %}
SELECT toFloat64('123.45')  -- Valid float
SELECT toFloat64OrZero('abc')  -- Returns 0.0 for invalid
SELECT toFloat64OrNull('abc')  -- Returns NULL for invalid
```

### Date and time parsing

Date parsing with proper formats:

```sql {% title="Date parsing" %}
SELECT toDate('2023-01-01')  -- YYYY-MM-DD format
SELECT toDateTime('2023-01-01 12:00:00')  -- YYYY-MM-DD HH:MM:SS
SELECT parseDateTime('01/01/2023', '%m/%d/%Y')  -- Custom format
```

### Boolean parsing

Boolean value parsing:

```sql {% title="Boolean parsing" %}
SELECT toBool('true')  -- Valid boolean (expects 'true'/'false' exactly)
SELECT toBool('false')  -- Valid boolean
SELECT toBool('1')  -- Valid boolean (1 = true)
SELECT toBool('0')  -- Valid boolean (0 = false)
```

### UUID parsing

UUID format parsing:

```sql {% title="UUID parsing" %}
SELECT toUUID('123e4567-e89b-12d3-a456-426614174000')  -- Valid UUID
```

## Advanced solutions

### Handling mixed data types

When dealing with columns that might contain different data types:

```sql {% title="Mixed type handling" %}
SELECT
    CASE
        WHEN text_column REGEXP '^[0-9]+$' THEN toInt32(text_column)
        WHEN text_column REGEXP '^[0-9]+\\.[0-9]+$' THEN toFloat64(text_column)
        WHEN text_column IN ('true', 'false', '1', '0') THEN toBool(text_column)
        ELSE text_column
    END as parsed_value
FROM events
```

### Working with JSON text

Parsing text from JSON data:

```sql {% title="JSON text parsing" %}
SELECT
    toInt32(JSONExtractString(data, 'user_id')) as user_id,
    toDateTime(JSONExtractString(data, 'timestamp')) as event_time,
    toBool(JSONExtractString(data, 'active')) as is_active
FROM events
```

### Extracting numbers from text

Use extract functions to parse numbers from text:

```sql {% title="Extract numbers from text" %}
SELECT extractAllInt64('abc123def456')  -- Returns [123, 456]
SELECT extractAllFloat64('price: 12.34, tax: 5.67')  -- Returns [12.34, 5.67]
```

### Error handling with try functions

Comprehensive error handling:

```sql {% title="Comprehensive error handling" %}
SELECT
    try(toInt32(text_column)) as safe_int,
    try(toFloat64(text_column)) as safe_float,
    try(toDate(text_column)) as safe_date,
    try(toBool(text_column)) as safe_bool
FROM events
```

## Data validation patterns

### Numeric validation

Validate numeric data before parsing:

```sql {% title="Numeric validation" %}
SELECT
    CASE
        WHEN text_column REGEXP '^[0-9]+$' THEN toInt32(text_column)
        WHEN text_column REGEXP '^[0-9]+\\.[0-9]+$' THEN toFloat64(text_column)
        ELSE NULL
    END as validated_number
FROM events
```

### Date validation

Validate date formats:

```sql {% title="Date validation" %}
SELECT
    CASE
        WHEN text_column REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$' THEN toDate(text_column)
        WHEN text_column REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$' THEN toDateTime(text_column)
        ELSE NULL
    END as validated_date
FROM events
```

## Tinybird-specific notes

In Tinybird, this error often occurs when:

* Ingesting data with inconsistent text formats
* Using Schema Hints with mismatched data types
* Processing JSON data with embedded text values
* Working with Data Sources that have mixed data types

To debug in Tinybird:

1. Verify your Data Source schema for type consistency.
2. Enforce proper types with Schema Hints.
3. Validate incoming data formats.
4. Use try functions in your Pipes for robust parsing.

## Best practices

### Data validation

* Validate data formats before conversion.
* Use try functions for robust error handling.
* Implement data quality checks.
* Log parsing errors for debugging.

### Type conversion

* Use appropriate conversion functions.
* Handle NULL values properly.
* Provide fallback values for invalid data.
* Test conversion functions with sample data.

### Error handling

* Implement comprehensive error handling.
* Use try functions for potentially problematic conversions.
* Log conversion errors for analysis.
* Provide meaningful error messages.

## See also

* [ClickHouse® Type Conversion Functions](/sql-reference/functions/type-conversion-functions)
* [Working with Data Sources](/forward/core-concepts/data-sources)
* [Common function errors](/sql-reference/clickhouse-errors/ILLEGAL_TYPE_OF_ARGUMENT)