SA0178 : LIKE operator is used without wildcards |
![]() |
The use of the LIKE operator without wildcard characters can lead to unexpected query behavior.

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:
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.

Resolve issues with the use of the LIKE operator without wildcards in SQL queries.
Follow these steps to address the issue:
-
Review the query to understand why LIKE is used without wildcards. If intended, clarify the reason in comments for future maintenance.
-
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’.
-
Consider the handling of trailing whitespace. Use = if ignoring trailing spaces, as LIKE considers every character, including trailing spaces.
-
If a pattern match is intended, include appropriate wildcards with LIKE (e.g., LIKE ‘Smith%’).
For example:
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%';

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

Name | Description | Default Value |
---|---|---|
IgnoreNonDMLStatements |
Parameter specifies whether to ignore LIKE predicate with pattern starting with “%” when it appears inside non-DML statements. |
yes |

The rule does not need Analysis Context or SQL Connection.


Design Rules, Bugs


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() |

Message | Line | Column | |
---|---|---|---|
1 | SA0178 : LIKE operator is used without wildcards. | 7 | 29 |
