CODEWITHSUNDEEP
mysqlsql-like
Dmytro
Dmytro

Reputation: 3

Multiple string literals in MySQL

Accidentally met something strange.

SELECT * from tablename WHERE mytext LIKE '%string1%' '%1234%';
works absolutely equal to
SELECT * from tablename WHERE mytext LIKE '%string1%' AND mytext LIKE '%1234%';

Something wrong with me, my code or I just didn't knew about it and couldn't find it in docs?

Upvotes: 0

Views: 222

Answers (1)

Pascal Bugnion
Pascal Bugnion

Reputation: 4928

String literals are automatically concatenated, as explained here.

For instance, 'hello' 'world' is equivalent to the single literal 'helloworld'

Thus, when you write:

SELECT * from tablename WHERE mytext LIKE '%string1%' '%1234%';

... that is actually equivalent to writing:

SELECT * from tablename WHERE mytext LIKE '%string1%%1234%';

As you noted, that is somewhat similar to:

SELECT * from tablename WHERE mytext LIKE '%string1%' AND mytext LIKE '%1234%';

... but not exactly. Indeed, the former would match 'string1 1234' but not '1234 string1'.

Try the following example to verify this:

create table tablename(
  pkey bigint,
  mytext varchar(64)
);

insert into tablename values (1, 'a b'), (2, 'a c'), (3, 'b a');
select * from tablename where mytext like '%a%' '%b%'; -- returns 1 row
select * from tablename where mytext like '%a%' and mytext like '%b%'; -- returns two rows

Upvotes: 1

Related Questions