CODEWITHSUNDEEP
sqlmysql
mike
mike

Reputation: 1

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

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.

Upvotes: 0

Views: 1447

Answers (2)

Rob Van Dam
Rob Van Dam

Reputation: 7970

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.

Upvotes: 3

Māris Kiseļovs
Māris Kiseļovs

Reputation: 17295

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

seems to be easiest way.

Upvotes: 0

Related Questions