SA0178 : LIKE operator is used without wildcards

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:

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:

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.

The rule does not need Analysis Context or SQL Connection.

Design Rules, Bugs

LIKE (Transact-SQL)

Other Resources