New Rule: always use wildcards with LIKE clauses

[silviomarghitola]

Language Usage / DMS & SQL / General
Always use wildcards in a LIKE clause

[PhilippSalvisberg]

from SonarSource:

"LIKE" clauses should not be used without wildcards

The use of LIKE in a SQL query without one or more wildcards in the sought value is suspicious. A maintainer can suppose that either = was meant instead, or that the wildcard was unintentionally omitted.

Note that in some cases using LIKE without a wildcard may return different results than the use of =. Thus, the use of LIKE without a wildcard may be intentional. However, it is highly likely to confuse maintainers who either are unaware of this fact, or don't understand that such circumstances apply to the query in question.

Noncompliant Code Example

SELECT name
FROM product
WHERE name LIKE 'choc'

Compliant Solution

SELECT name
FROM product
WHERE name LIKE 'choc%'

or

SELECT name
FROM product
WHERE name = 'choc'

[PhilippSalvisberg]

I do not know a case where a = 'something' leads to a different result than LIKE 'something'. Can someone provide an example?

However, using LIKE without wildcards does not make sense and this rule is useful.

Notes regarding PL/SQL Cop: a check should consider simple strings only to avoid false positives, e.g. when a variable is used or an expression with variables.

[rogertroller]

yep....i notice over and over again, that everyone is familiar with the % wildcard but many forget that _ is also a wild card. So you can easily find things like where table_name like 'XX_YYYYYY' and then the big surprise is, that more than one row shows up...

[PhilippSalvisberg]

You mean the wildcards like the underscore _ have a different meaning in a LIKE condition than in an equal condition and based on that, the result may change. I agree, but I do not agree with this:

Note that in some cases using LIKE without a wildcard may return different results than the use of =.

IMHO this is simply wrong.

[rogertroller]

I agree with your disagreement.

[NGrossman]

Weird, but not wrong.

select (case when 'something' LIKE 'something '
             then 'LIKE' else 'not LIKE'
        end) as "Like?",
       (case when 'something' = 'something '
             then 'EQUAL' else 'not EQUAL'
        end) as "Equal?"
from dual;

returns ('not LIKE', 'EQUAL') in 11g r2 [SQL Fiddle].
So does this

create table tmp (char2 char(2), char3 char(3));
insert into tmp values ('ch', 'ch ');

select (case when char2 LIKE char3
             then 'LIKE' else 'not LIKE'
        end) as "Like?",
       (case when char2 = char3
             then 'EQUAL' else 'not EQUAL'
        end) as "Equal?"
from tmp;

[SQL Fiddle]

ref: [Ask Tom]
[Equals(=) vs. LIKE (Stack Exchange)] The standards discussion is helpful, but idk how to translate his "Motivating Example" to Oracle.

[PhilippSalvisberg]

Thanks @NGrossman for sharing this. I was not aware of this issue. That's interesting. The first result is unexpected (wrong). The second one is correct.

It looks like it was a bug in old Oracle Database versions. I guess SQL Fiddle is using an old, unpatched 11gR2 version (maybe an XE). Now I can understand this explanation:

Note that in some cases using LIKE without a wildcard may return different results than the use of =.

And if you run a version older than 11.2.0.4.0 then this is in fact a problem. But this does not apply for new versions of the Oracle Database.

Adding new rules/reasonings targeting unpatched 11gR2 environments is wrong IMHO.

Here are some the result.

19.4.0.0.0 (looks good)

SQLcl: Release 19.1 Production on Fri Oct 04 09:30:11 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0


SQL> select (case when 'something' LIKE 'something '
  2               then 'LIKE' else 'not LIKE'
  3          end) as "Like?",
  4         (case when 'something' = 'something '
  5               then 'EQUAL' else 'not EQUAL'
  6          end) as "Equal?"
  7  from dual;

Like?    Equal?   
-------- ---------
not LIKE not EQUAL

SQL> create table tmp (char2 char(2), char3 char(3));

Table TMP created.

SQL> insert into tmp values ('ch', 'ch ');

1 row inserted.

SQL> select (case when char2 LIKE char3
  2               then 'LIKE' else 'not LIKE'
  3          end) as "Like?",
  4         (case when char2 = char3
  5               then 'EQUAL' else 'not EQUAL'
  6          end) as "Equal?"
  7  from tmp;

Like?    Equal?   
-------- ---------
not LIKE EQUAL    

11.2.0.4.0 (looks good)

SQLcl: Release 19.1 Production on Fri Oct 04 09:38:55 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option


SQL> select (case when 'something' LIKE 'something '
  2               then 'LIKE' else 'not LIKE'
  3          end) as "Like?",
  4         (case when 'something' = 'something '
  5               then 'EQUAL' else 'not EQUAL'
  6          end) as "Equal?"
  7  from dual;

Like?    Equal?   
-------- ---------
not LIKE not EQUAL

SQL> create table tmp (char2 char(2), char3 char(3));

Table TMP created.

SQL> insert into tmp values ('ch', 'ch ');

1 row inserted.

SQL> select (case when char2 LIKE char3
  2               then 'LIKE' else 'not LIKE'
  3          end) as "Like?",
  4         (case when char2 = char3
  5               then 'EQUAL' else 'not EQUAL'
  6          end) as "Equal?"
  7  from tmp;

Like?    Equal?   
-------- ---------
not LIKE EQUAL    

11.2.0.2.0 (looks wrong)

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 4 07:57:06 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select (case when 'something' LIKE 'something '
  2               then 'LIKE' else 'not LIKE'
  3          end) as "Like?",
  4         (case when 'something' = 'something '
  5               then 'EQUAL' else 'not EQUAL'
  6          end) as "Equal?"
  7  from dual;

Like?	 Equal?
-------- ---------
not LIKE EQUAL

SQL> create table tmp (char2 char(2), char3 char(3));

Table created.

SQL> insert into tmp values ('ch', 'ch ');

1 row created.

SQL> select (case when char2 LIKE char3
  2               then 'LIKE' else 'not LIKE'
  3          end) as "Like?",
  4         (case when char2 = char3
  5               then 'EQUAL' else 'not EQUAL'
  6          end) as "Equal?"
  7  from tmp;

Like?	 Equal?
-------- ---------
not LIKE EQUAL

9.2.0.8 (looks wrong)

SQLcl: Release 19.1 Production on Fri Oct 04 09:43:02 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production


SQL> select (case when 'something' LIKE 'something '
  2               then 'LIKE' else 'not LIKE'
  3          end) as "Like?",
  4         (case when 'something' = 'something '
  5               then 'EQUAL' else 'not EQUAL'
  6          end) as "Equal?"
  7  from dual;

Like?    Equal?   
-------- ---------
not LIKE EQUAL    

SQL> create table tmp (char2 char(2), char3 char(3));

Table TMP created.

SQL> insert into tmp values ('ch', 'ch ');

1 row inserted.

SQL> select (case when char2 LIKE char3
  2               then 'LIKE' else 'not LIKE'
  3          end) as "Like?",
  4         (case when char2 = char3
  5               then 'EQUAL' else 'not EQUAL'
  6          end) as "Equal?"
  7  from tmp;

Like?    Equal?   
-------- ---------
not LIKE EQUAL    

[S-Koell]

I would be for this rule.
If you want to compare if things are equal you should use "=".
If you are using "like" you want to pattern match something (also stated by the official documentation!).
So if you use like there should be at least one wildcard used (which is not escaped).

[PhilippSalvisberg]

Thanks. Ok, let's add this rule, without adding the paragraph starting with "Note".

[PhilippSalvisberg]

See also @rogertroller's blog post titled Antipattern SQL & PL/SQL – Using the LIKE operator even if searching for equality. Emphasizes the usefulness of this rule. Good example in combination with a CHAR based column (something to be avoided, see G-2310).