MySQL, select table if a word appears multiple times in a column

Question

I want to write a query to select from a table all rows that have the word "piggy" more than 3 times in a column called Description.

I know the query to select rows with the word "piggy" in a column called Description would be:

SELECT * FROM table WHERE Description LIKE "%piggy%"

so what do i need to do in order to select only if the word "piggy" occurs more than 3 times.

Rob Van Dam · Accepted Answer · 2010-11-20 07:07:03Z

3

Maris' answer is nice and easy (except I believe you wanted MORE than 3 times) but these can be customized more easily:

SELECT * FROM table WHERE DESCRIPTION LIKE CONCAT('%', REPEAT('piggy%', 4))

SELECT * FROM table WHERE Description != SUBSTRING_INDEX(Description, 'piggy', 4)

SUBSTRING_INDEX here returns the portion of the string to the left of the 4th occurrence of 'piggy' or the whole string if it doesn't match that many times (so the query throws away rows that match the whole string). Then if you change how many occurences you want, just change the number(s).

Of course, ALL of these options will be really slow if you have anything more than a few rows in your table.

answered Nov 20, 2010 at 7:07

Rob Van Dam

7,9803 gold badges34 silver badges35 bronze badges

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

Comments

Māris Kiseļovs · Accepted Answer · 2010-11-20 06:16:23Z

0

SELECT * FROM table WHERE Description LIKE "%piggy%piggy%piggy%"

seems to be easiest way.

answered Nov 20, 2010 at 6:16

Māris Kiseļovs

17.3k5 gold badges43 silver badges50 bronze badges

Collectives™ on Stack Overflow

MySQL, select table if a word appears multiple times in a column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related