Numeric Operations on Character Columns

Avoid comparing number literals to character columns. It requires that all of the strings compared be converted to numbers, which takes much longer than comparing two strings.

For example, suppose that you wish to find all customers within the 356 telephone exchange code:

SELECT lname FROM customer WHERE phone [5,7] = '356';

Notice that the operand whose value is 356 is enclosed in quotes. The quotes indicate that the database server must handle the filter as a character string. By contrast, when the operand is not in quotes, the server treats each retrieved value as a number, and must implicitly cast each value retrieved from the table to a numeric data type.

The following example causes implicit data type conversion of the phone substrings:

SELECT lname FROM customer WHERE phone [5,7] = 356;

If the UPDATE STATISTICS MEDIUM or UPDATE STATISTICS HIGH statement has been run on this column, the query optimizer tries to determine the selectivity of the predicate by matching the constant in the query with a substring of values saved in the distribution bin. Requiring data type conversion of every row in a character column so that it can be compared to a numeric filter needlessly increases the cost of the query that omits quotation mark delimiters around 356, compared to cost of the query in the first example.

Queries that compare character strings to numbers can fail with EM -1213 if the database server cannot convert the string. If you cannot avoid applying numeric filters to character values, only attempt such operations on character columns whose characters are restricted to digits in the range ASCII 0x30 through 0x39, and decimal point (ASCII 0x2e). This range is also known as seminumeric.

The database server does not use an index when DML statements compare a character column with a noncharacter value that is not equal in length to the character column.