SA0178 : LIKE operator is used without wildcards

Introduction

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:

-- Example of a potentially problematic query
SELECT * 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:

-- Corrected query using equals operator for exact match
SELECT * FROM Employees WHERE LastName = 'Smith';

-- Using LIKE operator with wildcard for pattern match
SELECT * 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.

Additional Information

LIKE (Transact-SQL)

Example Test SQL

SELECT     LocationID
FROM       Locations
WHERE      Specialities LIKE 'A__les'

SELECT     LocationID
FROM       Locations
WHERE      Specialities LIKE 'App'

SELECT     LocationID
FROM       Locations
WHERE      Specialities LIKE 'A[^p]'

SELECT     LocationID
FROM       Locations
WHERE      Specialities LIKE 'Apples' /*IGNORE:SA0178*/


SELECT     LocationID
FROM       Locations /*IGNORE:SA0178(STATEMENT)*/
WHERE      Specialities LIKE 'pples'

IF db_name() like '%za%'
PRINT db_name()

Analysis Results

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

Analysis Rules