SQL - Find complete word in text

Question

I have a text column in one of my tables in a MySql DB. i want to get all the records that have a specific word in the text column. for example:

ID,BIO
1, "i am a cto"
2, "i am a cto/developer"
3, "cto"
4, "i am a cto_developer"
5, "ctools"

In this case, when searching for "cto" I want the query to return records 1,2,3,4 but not 5.

Any Ideas?

p.s. I want it to be case insensitive

Answer 1

use LIKE operator. like so:

select * from table1 where BIO LIKE '%cto%'  

the '%' indicates a wildcard (meaning- any set of chars).
if you want to find only records that begin with 'cto', use: 'cto%'.
if you want only records that contain cto as a full word (i.e with spaces around it), use:

BIO LIKE '% cto %' or BIO LIKE 'cto %' or BIO LIKE 'cto %'  

(the two Or's i've added are for cases when 'cto' appears at the beginning / end of the string)

Answer 2

You may want to look into using full text indexing depending on the amount of data you are using. Otherwise, you can use REGEXP to specific a regular expression to search for the word. You should see this question (and answer) for a way of using REGEXP to find words.