Consultor Eletrônico

Status: Unverified

GOAL:

SQL: How to use the LIKE predicate against INTEGER fields?

GOAL:

How can the LIKE predicate be used against INTEGER fields?

GOAL:

SQL: How to use the LIKE predicate against DECIMAL fields?

GOAL:

How can the LIKE predicate be used against DECIMAL fields?

FACT(s) (Environment):

All Supported Operating Systems
Progress 9.1x
OpenEdge 10.x

FIX:

The LIKE predicate can not be used directly against an INTEGER or a DECIMAL field because the LIKE predicate operates on strings and not on numbers. For example running the query under ODBC or JDBC:
SELECT
*
FROM
pub.customer
WHERE
"Cust-Num" like '2%'
will generate the error: Invalid number string (7498) .
Since the LIKE predicate acts on strings and character data and not on numeric data, the INTEGER or DECIMAL field must be converted to its character representation allow the use of the LIKE predicate. In other words, the LIKE predicate acts on the character representation of INTEGER and DECIMAL fields and not on these fields directly. For example, the following two queries execute correctly thanks to the conversion of the INTEGER Cust-Num field to its character representation using the CAST and the CONVERT functions:
1. Using the CAST function:
SELECT
*
FROM
pub.customer
WHERE
CAST("Cust-Num" AS CHAR(25), 12) like '1%'
2. Using the CONVERT function:
SELECT
*
FROM
pub.Customer
WHERE
CONVERT('VARCHAR(25)', "Cust-Num") LIKE '2%'