SA0178 : LIKE operator is used without wildcards

The topic describes the SA0178 analysis rule.

Message

LIKE operator is used without wildcards

Description

This rule checks for LIKE operator usages without wildcard characters.

The use of LIKE without one or more wildcard characters in the pattern is probably a mistake.

How to fix

Review the query and the reason LIKE is used without wild cards. If the wildcards are not missed by mistake, consider using ‘=’ operator.

Note that there is a difference between the equals operator and LIKE operator in terms of handling trailing whitespace.

For the LIKE operator every character is significant, while the equal sign will ignore any trailing whitespace.

For example:

SELECT * FROM sys.all_objects WHERE name =’objects ‘ — returns 1 row

SELECT * FROM sys.all_objects WHERE name LIKE ‘objects ‘ — returns 0 rows

SELECT * FROM sys.all_objects WHERE name LIKE ‘objects’ — returns 1 row

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
Copy
 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

Analysis Rules