1

I have a question, what is the best way to find an exact string match within a column.

I tried using locate('needle', 'haystack') > 0. The problem with this is, for instance if the string I am trying to find is something like 'Ed', but inside a blob or a text column, I have a string that says 'I lived', locate() would return 6. However, this is not what I am asking. In an exact match, it would be best to use LIKE '', however, LIKE has it's performance issues, therefore, it is not a viable solution.

Is there a way I an use LOCATE() to do an exact match?

Anirudh Ramanathan
  • 46,179
  • 22
  • 132
  • 191
ericg
  • 103
  • 8
  • You say LIKE has performance issues, but you're not going to solve the performance issue by using a function that does exactly what LIKE does. – Darius X. Mar 11 '13 at 21:08
  • What performance problems are you experiencing with `LIKE` that you aren't experiencing with `LOCATE`? Both methods will obfuscate any indexes, so they should perform relatively the same - [and based on this answer, `LIKE` marginally out-performs `LOCATE`](http://stackoverflow.com/a/7500150/643591). – Michael Fredrickson Mar 11 '13 at 21:08

1 Answers1

3

You can use this:

WHERE CONCAT(' ', column, ' ') LIKE BINARY '% string_to_find %'

or using LOCATE:

WHERE LOCATE(BINARY ' Ed ', CONCAT(' ', column, ' '))

Using BINARY will force an exact case matching. I think that performance of LOCATE or LIKE will be very similar. Please see fiddle here.

fthiella
  • 48,073
  • 15
  • 90
  • 106
  • Thanks! This actually works. I wasn't sure how to concatenate white spaces around the column value. This gives me a higher probability of finding a match. Thanks again. – ericg Mar 14 '13 at 15:23