CODEWITHSUNDEEP
mysql
ericg
ericg

Reputation: 103

How to find an exact string within a mysql column

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?

Upvotes: 1

Views: 1673

Answers (1)

fthiella
fthiella

Reputation: 49049

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.

Upvotes: 3

Related Questions