Kbase P98363: Can the LIKE predicate of a prepared SQL-92 statement be used to search an index when its search pat
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/2/2004 |
|
Status: Unverified
GOAL:
Can the LIKE predicate of a prepared SQL-92 statement be used to search an index when its search pattern is a parameter?
FIX:
No, because with queries like:
SELECT * FROM Customer WHERE Name LIKE ?.
SQL-92 determines how to execute the query, including all uses of indexes, at Prepare time. When a LIKE predicate has a parameter as its "pattern string", there is no way to know what the actual runtime value will be. The value might be '%' or '%shir%' or '%man'. Values like these cannot be used to search an index. So, because SQL-92 does not know the LIKE value, the LIKE predicate cannot be used to search an index:
On the other hand, with queries like:
SELECT * FROM Customer WHERE Name LIKE 'Jill J%'.
SQL-92 knows that the name must start with 'Jill J' and so can build a search using an index. Therefore, in this case, SQL-92 builds a search based on that known value.