2012-04-18Numeric Strings

Numeric strings are numbers stored in string fields. That is unusual, but not a problem when using string comparisons:

SELECT ...
  FROM ...
 WHERE numeric_string = '42'

That statement can, of course, use an index on NUMERIC_STRING. But it cannot be used as access predicate when using a real number:

SELECT ...
  FROM ...
 WHERE numeric_string = 42

Note the missing quotes. The type mismatch causes the database to perform an implicit conversion like this:

SELECT ...
  FROM ...
 WHERE TO_NUMBER(numeric_string) = 42

It is the same problem as before. An index on NUMERIC_STRING cannot be used properly due to the function call. The solution is, of course, the same as before. Do not convert the table column, but the search term.

SELECT ...
  FROM ...
 WHERE numeric_string = TO_CHAR(42)

You might wonder why the database does not do it this way? It is because parsing a string as number delivers an unambiguous result. That does not hold true the other way around. A number, formatted as text, may contain spaces, or thousands separators, or leading zeros:

42
042
0042
00042
...

The database cannot know the number format used in the NUMERIC_STRING column. Therefore, the conversion is done the other way around; the strings are converted to numbers—that is an unambiguous transformation.

But if you use the TO_CHAR approach, you will get just one string representation of the number. The query will therefore only match the first of above listed strings. The TO_NUMBER conversion matches all of them. That means there is not only a performance difference between the two variants, but also a semantic difference!

Using numeric strings is looking for trouble: that is the semantic conversion difference; the performance problems caused by implicit conversion; and, last but not least, the risk of invalid numbers causing conversion errors. Even the most trivial query, not using any functions in the where clause, can abort with a conversion error.

The problem does not exist the other way around, by the way:

SELECT ...
  FROM ...
 WHERE numeric_number = '42'

The database will, consistently, transform the string into a number. However, that does not wrap the—potentially indexed—column by any function. A regular index will work. But a manual conversion can still be done in the wrong way:

SELECT ...
  FROM ...
 WHERE TO_CHAR(numeric_number) = '42'

Using the TO_CHAR function on an indexed column renders the expression unusable as access predicate.