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

Please be aware that all the answers you're getting involving LIKE '%foo%' or INSTR force a full scan. This will be slow if your table is big. They can not do an index lookup. — derobert
– derobert, Commented Oct 21, 2011 at 21:25
thanks what would i use instead if i wanted to maximize speed. my table is big so speed is an issue — rob melino
– rob melino, Commented Oct 21, 2011 at 21:32
if speed is critical, you need an indexing solution such as MySQL fulltext or Sphinx. — derobert
– derobert, Commented Oct 21, 2011 at 21:43

Mark Byers · Accepted Answer · 2011-10-21 21:20:37Z

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.

answered Oct 21, 2011 at 21:20

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rob melino Over a year ago

thank you, this makes sense. how would i change this for searching for results that DID NOT have the word dog in them?

rob melino Over a year ago

figure this out, use NOT LIKE

Salvatore Previti · Accepted Answer · 2011-10-21 21:20:03Z

0

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.

answered Oct 21, 2011 at 21:20

Salvatore Previti

9,11033 silver badges38 bronze badges

Comments

ville6000 · Accepted Answer · 2011-10-21 21:21:05Z

0

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

answered Oct 21, 2011 at 21:21

ville6000

1518 bronze badges

Collectives™ on Stack Overflow

mysql query that looks for a value in a string

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related