SA0178 : LIKE operator is used without wildcards

The use of the LIKE operator without wildcard characters can lead to unexpected query behavior.

Description

The LIKE operator is commonly used to search for a specified pattern within a column. However, using LIKE without any wildcard characters (such as % or _) is effectively equivalent to using the = operator for string comparison. This is prone to errors as it might not be the intended operation, leading to potential issues in query logic.

For example:

SQL
1-- Example of a potentially problematic query
2SELECT * FROM Employees WHERE LastName LIKE 'Smith';

In the above query, using LIKE without wildcards makes it functionally identical to WHERE LastName = ‘Smith’. This might not be what the database administrator intends, especially if they mean to perform a broader pattern match.

  • This can lead to inefficient queries that don’t leverage full text-search capabilities when wildcards are not used.

  • It can result in future maintenance issues, as the intention behind using LIKE might not be clear to others examining the code.

How to fix

Resolve issues with the use of the LIKE operator without wildcards in SQL queries.

Follow these steps to address the issue:

  1. Review the query to understand why LIKE is used without wildcards. If intended, clarify the reason in comments for future maintenance.

  2. If the use of LIKE is intended for exact matches, replace it with the = operator for better performance and clarity. For example, update WHERE LastName LIKE ‘Smith’ to WHERE LastName = ‘Smith’.

  3. Consider the handling of trailing whitespace. Use = if ignoring trailing spaces, as LIKE considers every character, including trailing spaces.

  4. If a pattern match is intended, include appropriate wildcards with LIKE (e.g., LIKE ‘Smith%’).

For example:

SQL
1-- Corrected query using equals operator for exact match
2SELECT * FROM Employees WHERE LastName = 'Smith';
3
4-- Using LIKE operator with wildcard for pattern match
5SELECT * FROM Employees WHERE LastName LIKE 'Smith%';

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
Name Description Default Value
IgnoreNonDMLStatements

Parameter specifies whether to ignore LIKE predicate with pattern starting with “%” when it appears inside non-DML statements.

yes

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
5 minutes per issue.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
 1SELECT     LocationID
 2FROM       Locations
 3WHERE      Specialities LIKE 'A__les' 
 4
 5SELECT     LocationID
 6FROM       Locations
 7WHERE      Specialities LIKE 'App'
 8
 9SELECT     LocationID
10FROM       Locations
11WHERE      Specialities LIKE 'A[^p]'
12
13SELECT     LocationID
14FROM       Locations
15WHERE      Specialities LIKE 'Apples' /*IGNORE:SA0178*/
16
17
18SELECT     LocationID
19FROM       Locations /*IGNORE:SA0178(STATEMENT)*/
20WHERE      Specialities LIKE 'pples' 
21
22IF db_name() like '%za%' 
23PRINT db_name()

Analysis Results
  Message Line Column
1 SA0178 : LIKE operator is used without wildcards. 7 29
See Also

Other Resources