MySQL Counting Matches?

[RussellReal]

lets say I have a table and it has the following results:

 

__________________________________________________

ID | STRING

--------------------------------------------------

1 | Hey, how are you?

2 | Hey, you said 'hey'!

 

and I wanted to use mysql regex to match 'hey' for example:

SELECT * FROM `table` WHERE `STRING` REGEXP 'hey'

 

I know I could do this other ways.. but I want to know how I can order by the number of 'hey's in the string.. because reading 150 something rows into php and then doing the sorting in PHP will be retarded.. it must be possible I just searched mysql.com up and down and havn't found anything of use..

 

Thanks guys, appreciate the time

[Ken2k7]

I don't think you can do that with just SQL.

[fenway]

I don't think you can do that with just SQL.

Well, not exactly, but you can "cheat" by using REPLACE() to replace "hey" with a single character, and then count the difference in lengths, knowing the length of your target string.  It's ugly, but it works like a charm.  I've used this before to count spaces or commas.