Safe Numeric Conversion in Oracle SQL

An analysis of NULL semantics, NLS settings, conversion errors, data quality, and rounding when numeric values are parsed from text in Oracle SQL.

When a text column stores values such as:

12
adet:12
adet: 12
12,5
1.250
unknown

calculating a total is not merely a data-type conversion. The system must first define the grammar used to interpret the text, the meaning of an invalid value, and the effect of unconvertible records on the aggregate.

The following Oracle expression can appear safe and sufficient at first glance:

ROUND(
    SUM(
        TO_NUMBER(
            CASE
                WHEN SUBSTR(ACIKLAMA, 1, 5) = 'adet:'
                    THEN SUBSTR(ACIKLAMA, 6)
                ELSE ACIKLAMA
            END
            DEFAULT 0 ON CONVERSION ERROR
        )
    )
) AS N

It removes the adet: prefix, converts the remaining text to a number, substitutes zero after a conversion failure, and sums the values. Its real reliability still depends on NULL, empty strings, decimal and group separators, whitespace, letter case, and the chosen data-quality policy.

What the Expression Actually Means

The inner CASE performs these transformations:

ACIKLAMA = "adet:12"  -> "12"
ACIKLAMA = "12"       -> "12"
ACIKLAMA = "Adet:12"  -> "Adet:12"
ACIKLAMA = " adet:12" -> " adet:12"

SUBSTR(ACIKLAMA, 1, 5) returns the first five characters. When they match, SUBSTR(ACIKLAMA, 6) returns the text from the sixth character to the end because no length is specified.

The prefix rule is therefore narrow:

remove the prefix only when the first five characters are exactly "adet:"

These inputs behave differently:

adet:12    -> 12
adet: 12   -> " 12"
Adet:12    -> "Adet:12"
ADET:12    -> "ADET:12"
adet :12   -> "adet :12"
 adet:12   -> " adet:12"

This is not inherently wrong. If the producer guarantees exactly one format, the rule is simple and fast. It may fail to match the real contract when the column contains manual input, legacy output, or data produced by several systems.

The next expression:

TO_NUMBER(expr DEFAULT 0 ON CONVERSION ERROR)

returns 0 when expr cannot be converted to a number. Oracle applies the default only to a conversion error. It does not catch an error raised while calculating expr, and the default value itself must be convertible to the target type.

The distinction is:

expression evaluation succeeds,
numeric conversion fails
-> DEFAULT is applied

expression evaluation itself fails
-> DEFAULT is not applied

The current CASE and SUBSTR operations have little risk on ordinary character data. More complex user functions, JSON processing, or error-prone arithmetic placed inside TO_NUMBER should not treat DEFAULT ON CONVERSION ERROR as a general exception handler.

NULL, Empty Text, and Invalid Text

DEFAULT 0 ON CONVERSION ERROR does not necessarily convert NULL to zero. TO_NUMBER(NULL) raises no conversion error and returns NULL.

Oracle currently treats a zero-length character value as NULL. It also distinguishes NULL from numeric zero.

The outcomes are therefore:

ACIKLAMA = NULL  -> TO_NUMBER(NULL) -> NULL
ACIKLAMA = ""    -> NULL in Oracle -> NULL
ACIKLAMA = "abc" -> conversion error -> 0
ACIKLAMA = "0"   -> 0

Oracle aggregate functions such as SUM ignore NULL. When a group contains no rows, or every argument is NULL, the result of SUM is also NULL.

The expression therefore implements this policy:

invalid text -> contributes zero
NULL         -> excluded from the aggregate
actual zero  -> contributes zero

The numerical result can make these cases appear equivalent, but their data-quality meaning is different:

0      -> measured value
NULL   -> unknown or absent value
"abc"  -> syntactically invalid record

Converting every invalid value to zero keeps the report running, but it can hide corruption. If thousands of records are accidentally written as adett:12 instead of adet:12, the query does not fail. It silently counts all of them as zero.

Safe conversion and data-quality monitoring are therefore separate requirements.

NLS Settings Can Change the Meaning

Without an explicit format model, TO_NUMBER interprets character data according to the session's numeric conventions. Decimal and group separators depend on NLS_NUMERIC_CHARACTERS. The setting contains two characters: the first is the decimal separator and the second is the group separator. A client or JDBC configuration can override the database's initial session value.

With:

NLS_NUMERIC_CHARACTERS = '.,'

Oracle can interpret:

1.25  -> one and twenty-five hundredths
1,250 -> one thousand two hundred fifty

With:

NLS_NUMERIC_CHARACTERS = ',.'

the roles are reversed:

1,25  -> one and twenty-five hundredths
1.250 -> one thousand two hundred fifty

The same SQL can therefore produce different numbers under another JDBC client or session. A conversion error is not always the most dangerous outcome. Some strings are valid in both settings but have different meanings:

"1.234"

It can mean:

decimal separator is dot -> 1.234
group separator is dot   -> 1234

DEFAULT 0 ON CONVERSION ERROR provides no protection because the conversion succeeded. The wrong semantics were applied successfully.

When the format is known, it should be declared explicitly:

TO_NUMBER(
    value DEFAULT 0 ON CONVERSION ERROR,
    '999999999D999999',
    'NLS_NUMERIC_CHARACTERS = '',.'''
)

In Oracle number format models, D represents the configured decimal character and G the group separator. The third TO_NUMBER argument can set conversion-specific NLS behavior.

When only integers are valid, the contract should be narrower. An unconstrained TO_NUMBER call can accept scientific notation, signs, decimals, or other session-dependent forms. If the field means a count, the system must explicitly answer questions such as:

Is 12.7 a valid count?
Is -5 valid?
Should 1E3 be accepted as 1000?

A technically successful conversion does not prove that the business rule was satisfied.

DEFAULT and VALIDATE_CONVERSION

If the only objective is to keep a report running, replacing invalid input with zero may be enough:

TO_NUMBER(value DEFAULT 0 ON CONVERSION ERROR)

When the objective includes measuring data quality, validity should be retained separately. Oracle's VALIDATE_CONVERSION function can test whether an expression can be converted to a specified type.

For example:

CASE
    WHEN VALIDATE_CONVERSION(value AS NUMBER) = 1
        THEN TO_NUMBER(value)
    ELSE NULL
END

This separates invalid text as NULL instead of replacing it with zero. The same data set can then produce both a total and an invalid-record count:

SELECT
    SUM(
        TO_NUMBER(value DEFAULT NULL ON CONVERSION ERROR)
    ) AS TOPLAM,
    SUM(
        CASE
            WHEN value IS NOT NULL
             AND VALIDATE_CONVERSION(value AS NUMBER) = 0
                THEN 1
            ELSE 0
        END
    ) AS GECERSIZ_KAYIT
FROM ...

The report distinguishes:

sum of valid numbers
number of unconvertible records

This is safer than silent zero substitution in critical reporting because these two data sets can produce the same total:

100 valid zeros
100 malformed strings

A total of zero does not establish that the data is correct.

Using VALIDATE_CONVERSION and then TO_NUMBER can evaluate the same conversion twice. When only a total is required over a large data set, this may be simpler:

TO_NUMBER(value DEFAULT NULL ON CONVERSION ERROR)

When a quality metric is also needed, the converted value can be calculated in a subquery:

SELECT
    SUM(NUMERIC_VALUE) AS TOPLAM,
    SUM(
        CASE
            WHEN RAW_VALUE IS NOT NULL
             AND NUMERIC_VALUE IS NULL
                THEN 1
            ELSE 0
        END
    ) AS GECERSIZ_KAYIT
FROM (
    SELECT
        RAW_VALUE,
        TO_NUMBER(
            RAW_VALUE DEFAULT NULL ON CONVERSION ERROR
        ) AS NUMERIC_VALUE
    FROM ...
)

The visual structure of a query does not prove how many times Oracle evaluates an expression. Performance decisions should still be based on the execution plan and measurement.

A More Explicit Parsing Contract

If the field accepts only:

12
adet:12

then the original CASE is reasonably simple and deterministic. Whitespace tolerance can be added explicitly:

TRIM(
    CASE
        WHEN SUBSTR(ACIKLAMA, 1, 5) = 'adet:'
            THEN SUBSTR(ACIKLAMA, 6)
        ELSE ACIKLAMA
    END
)

If case-insensitive prefixes are also accepted:

CASE
    WHEN LOWER(SUBSTR(ACIKLAMA, 1, 5)) = 'adet:'
        THEN SUBSTR(ACIKLAMA, 6)
    ELSE ACIKLAMA
END

can be used. Every LOWER, TRIM, regular expression, or other function applied to the column adds work. Cleaning free-form text with regular expressions across hundreds of thousands or millions of rows is more expensive than modeling the source data correctly.

A stronger version is:

ROUND(
    SUM(
        TO_NUMBER(
            TRIM(
                CASE
                    WHEN SUBSTR(ACIKLAMA, 1, 5) = 'adet:'
                        THEN SUBSTR(ACIKLAMA, 6)
                    ELSE ACIKLAMA
                END
            )
            DEFAULT 0 ON CONVERSION ERROR
        )
    )
) AS N

It produces:

"adet:12"  -> 12
"adet: 12" -> 12
" 12 "     -> 12
"abc"      -> 0
NULL       -> NULL

If the field must contain integers, the outer ROUND needs separate semantic review. This expression:

ROUND(SUM(...))

sums decimal values and then rounds the total. It is not equivalent to:

SUM(ROUND(...))

For:

0.6 + 0.6

The results are:

ROUND(0.6 + 0.6) = 1
ROUND(0.6) + ROUND(0.6) = 2

If the column contains counts, each row may be expected to be integral. Rounding only after aggregation can silently accept malformed decimal input. The business rule must choose one behavior:

Reject decimal counts.
Round each row.
Sum decimals and round only for presentation.

These policies produce different results. The SQL expression cannot decide the business rule by itself.

Numeric Data in Text Is Technical Debt

Storing a numeric value in VARCHAR2 repeats the same costs in every query:

Prefix parsing
Whitespace cleanup
NLS interpretation
Conversion-error handling
Ambiguous data quality
Function execution cost
Difficult indexing

A better model stores the number and the description separately:

ADET       NUMBER
ACIKLAMA   VARCHAR2

If the source schema cannot be changed, a virtual column, materialized view, or clean numeric field produced during ETL can be considered:

ADET_SAYI GENERATED ALWAYS AS (
    TO_NUMBER(
        TRIM(
            CASE
                WHEN SUBSTR(ACIKLAMA, 1, 5) = 'adet:'
                    THEN SUBSTR(ACIKLAMA, 6)
                ELSE ACIKLAMA
            END
        )
        DEFAULT NULL ON CONVERSION ERROR
    )
) VIRTUAL

Feasibility depends on the Oracle version, expression restrictions, and schema privileges. When the schema cannot change, the same parsing rule can remain in the query layer, but it should be defined at one central SQL generation point. Interpreting the same field differently in separate reports causes totals to diverge.

The main engineering problem is not making TO_NUMBER run without an exception. It is defining a clear contract between text and number:

Which strings are valid?
What are the decimal and group separators?
What does NULL mean?
Is malformed input zero, missing data, or an error?
Are negative values allowed?
Are decimals allowed?

DEFAULT 0 ON CONVERSION ERROR supports operational continuity, but it does not guarantee data correctness. A silently generated total can be more dangerous than a query that fails visibly. If conversion errors are hidden, data-quality information should become visible elsewhere.

Reliable SQL does more than calculate valid data correctly. It also defines exactly how invalid data enters the result.