Home
LIKE clauses should not start with wildcard characters
Description
The rule "LIKE clauses should not start with wildcard characters" in PL/SQL code states that when using the LIKE operator in a query, the search pattern should not begin with a wildcard character. This is because starting a LIKE clause with a wildcard character can cause the query to take a long time to execute as the database engine must search through all possible matches before returning the result. Additionally, starting a LIKE clause with a wildcard character can lead to incorrect results if the search pattern is not well defined. Therefore, it is best practice to avoid beginning LIKE clauses with wildcard characters in PL/SQL code.
Key Benefits
- Improved Performance: LIKE clauses should not start with wildcard characters as they can cause the query to perform a full table scan, as opposed to an index scan, which can be time consuming.
- Increased Accuracy: By avoiding wildcard characters at the beginning of LIKE clauses, the query is more likely to return accurate results.
Non-compliant Code Example
SELECT
NAME,
ADDRESS,
CREDIT_LIMIT
FROM
CUSTOMERS
Where ADDRESS like '%BUNGALOWS%'; --Non compliant code (Like clause starting with wild-cards)
Compliant Code Example
SELECT
NAME,
ADDRESS,
CREDIT_LIMIT
FROM
CUSTOMERS
Where ADDRESS like 'BUNGALOWS%'; --Compliant code (Like clause is not starting with wild-cards)