mysql query that looks for a value in a string

Question

I am wondering if i can put something in my query which searches for a word within a string. For example, if i have a field named user_purchased, i want to query it and pull only records where the word 'dog' exists within the string.

I know i can do this using php like this

(!stristr($row['user_purchased'], 'dog'))

BUT, i'm wondering if i can do this in the query itself instead and that this will perhaps speed up performance.

Thanks in advance for help on this

Answer 1

You can use LIKE:

WHERE your_col LIKE '%dog%'

If you want better performance you could look at a full text search. This uses a FULLTEXT index to quickly find the matching rows.

Answer 2

In MySql, you can use INSTR(str,substr)

Quote from the MySql manual at http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_instr

INSTR(str,substr)

Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed.

mysql> SELECT INSTR('foobarbar', 'bar');
        -> 4
mysql> SELECT INSTR('xbar', 'foobar');
        -> 0
This function is multi-byte safe, and is case sensitive only if at least one argument is a binary string.

Answer 3

SELECT * FROM table WHERE user_purchased LIKE '%dog%';