CODEWITHSUNDEEP
sqlpostgresql
zebrainatree
zebrainatree

Reputation: 361

Why do I get different results if I put a wildcard in a string BEFORE a LIKE as opposed to a string AFTER a LIKE

I ran these two queries in PostgreSQL and got different results. I was wondering if someone could explain why. Is this the same in MicrosoftSQL, MySQL, and other databases?

select 'teststring%' like 'teststring'; --returns False
select 'teststring' like 'teststring%'; --returns True

Upvotes: 0

Views: 137

Answers (2)

rslemos
rslemos

Reputation: 2731

Because 'teststring%' is actually not like 'teststring'.

But 'teststring' is like 'teststring%'.

This should happen in every database.

LIKE operator is not commutative. If the rightside string has no wildcard character (ampersand or underscore) it behaves as = (as a side effect).

Algorithm for LIKE handles the rightside this way:

  • %: matches any number of any characters (could match none);
  • _: matches any character (no more, no less), but just exactly one;
  • any other char: matches that character solely.

The leftside string has no special meaning to any character.

Upvotes: 3

Houari
Houari

Reputation: 5631

After the like pattern, the % character is considered as escape character. but it is not the same case for the first % (which is before like)

to escape the character % you have to add \ (This the default one in PostgreSQL, you can choose another using the escape clause): \%

For example:

select 'teststring%' like 'teststring'; -- returns false
select 'teststring' like 'teststring%'; -- returns true
select 'teststring%' like 'teststring%'; -- returns true
select 'teststring%' like 'teststring\%'; -- returns true

Upvotes: 1

Related Questions