by Markus Winand.

Numeric Strings

Numeric strings are numbers that are stored in text columns. Although it is a very bad practice, it does not automatically render an index useless if you consistently treat it as string:

  FROM ...
 WHERE numeric_string = '42'

Of course this statement can use an index on NUMERIC_STRING. If you compare it using a number, however, the database can no longer use this condition as an access predicate.

  FROM ...
 WHERE numeric_string = 42

Note the missing quotes. Although some database yield an error (e.g. PostgreSQL) many databases just add an implicit type conversion.

  FROM ...
 WHERE TO_NUMBER(numeric_string) = 42

It is the same problem as before. An index on NUMERIC_STRING cannot be used due to the function call. The solution is also the same as before: do not convert the table column, instead convert the search term.

  FROM ...
 WHERE numeric_string = TO_CHAR(42)

You might wonder why the database does not do it this way automatically? It is because converting a string to a number always gives an unambiguous result. This is not true the other way around. A number, formatted as text, can contain spaces, punctation, and leading zeros. A single value can be written in many ways:


The database cannot know the number format used in the NUMERIC_STRING column so it does it the other way around: the database converts the strings to numbers—this is an unambiguous transformation.

On my Own Behalf

I offer training, tuning and consulting. Buying my book “SQL Performance Explained” (from €9.95) also supports my work on this website.

The TO_CHAR function returns only one string representation of the number. It will therefore only match the first of above listed strings. If we use TO_NUMBER, it 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 generally troublesome: most importantly it causes performance problems due to the implicit conversion and also introduces a risk of running into conversion errors due to invalid numbers. Even the most trivial query that does not use any functions in the where clause can cause an abort with a conversion error if there is just one invalid number stored in the table.


Use numeric types to store numbers.

Note that the problem does not exist the other way around:

  FROM ...
 WHERE numeric_number = '42'

The database will consistently transform the string into a number. It does not apply a function on the potentially indexed column: a regular index will therefore work. Nevertheless it is possible to do a manual conversion the wrong way:

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

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at Previously he made, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via

Buy his Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at

Hire Markus

Markus offers SQL training and consulting for developers working at companies of any size.
Learn more »

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

The Use The Index, Luke! mug

Stickers, coasters, books and coffee mugs. All you need for learning.

Shop now

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license