CODEWITHSUNDEEP
mysqlsqlcollation
Alex
Alex

Reputation: 3571

sql search for substring using LIKE vs locate()

I'm trying to write a query to find a string that contains a substring. What's the difference between the following two methods?

  1. SELECT * FROM table WHERE names LIKE '%bob%'
  2. SELECT * FROM table WHERE locate('bob', names)

I first learned to use LIKE, but I was having "Illegal mix of collations.." error saying that I'm comparing English (latin) and Chinese (big5). While googling methods to fix collation problem I came across locate(), which didn't complain about collations when I tried it. Just wondering if I can keep using locate() or if there's any downsides to it.

Upvotes: 1

Views: 3963

Answers (2)

Olivier Picault
Olivier Picault

Reputation: 348

They are simply not doing the same thing. LIKE is an operator which is doing a text matching while LOCATE is a function which returns the position of the first occurrence.

http://www.w3resource.com/mysql/string-functions/mysql-locate-function.php http://www.w3resource.com/mysql/string-functions/mysql-like-function.php

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269853

To a large extent, it is a matter of taste.

The most important consideration is the like is ANSI standard SQL and supported by most (if not all) databases. locate is specific to MySQL, so it is not necessarily portable to other databases.

Some databases optimize the expression x like 'y%' to use an index. MySQL doesn't, so this is not a consideration for you.

Another advantage to like is that it supports initial matches ('y%') and final matches ('%y') as well as intermediate matches. One method for all three. The equivalent statements using locate are more complex.

My preference is to use like, especially because of the portability. However, I am not typically confronted with dealing with different character sets. You may be better off learning about collations in MySQL than in trying to find work-arounds.

Upvotes: 1

Related Questions