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)
Visual Expert 2024
 VEPLSQLRULE92