2

I have the below MySQL query.

select * from node where title LIKE 'born in care shelter breed';

Which is returning empty set. But when I try the below query

select * from node where title = 'born in care shelter breed';

It is returning 1 result.

What difference the both will make? I can't avoid the LIKE operator as the query creating after some condition checking

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Arun
  • 3,640
  • 7
  • 44
  • 87
  • 2
    Other possible answer: https://stackoverflow.com/questions/543580/equals-vs-like – Nico Haase Jul 20 '18 at 09:42
  • which DB engine you are using and what is the characterset of table/column. Can you please add the table structure. – Vivek Jul 20 '18 at 09:46
  • try this: `select * from node where trim(title) LIKE trim('born in care shelter breed');` vs `select * from node where trim(title) = trim('born in care shelter breed');` –  Jul 20 '18 at 10:20
  • @ADyson I updated my answer, had no idea about the trailing whitespace thing. Yes, I read the potential duplicates, obviously not thoroughly enough to see those bits. It's pretty hard to find in the MySQL doco. Thanks for informing me, you learn something new every day – e_i_pi Jul 20 '18 at 10:31

2 Answers2

0

I'm guessing you have trailing whitespace in your title field. MySQL string comparison using the = sign does not consider trailing whitespace. Note the following:

CREATE TABLE node
(title VARCHAR(99));

INSERT INTO node (title)
VALUES ('born in care shelter breed '); -- Note the space at the end

SELECT * FROM node WHERE title LIKE 'born in care shelter breed';
SELECT * FROM node WHERE title = 'born in care shelter breed';

Notice how the first select statement returns zero results, but the second one finds the row.

SQL Fiddle

The documentation talks about this on the string comparison page, stating:

In particular, trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator

e_i_pi
  • 4,590
  • 4
  • 27
  • 45
-1

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards used in conjunction with the LIKE operator:

% - The percent sign represents zero, one, or multiple characters
_ - The underscore represents a single character

i think below will return row

select * from node where title LIKE '%born in care shelter breed%';

To know about like more

Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63