str_count(needle, haystack) function in mysql?

sfullman

Is there a function which returns a count of the occurences of a needle string in a haystack field?

I searched and nothing, also I can't find my favorite function and concept index page on dev.mysq.com anymore.

Sam

devinemke

you should read up on mySQL String Functions. look at FIND_IN_SET().

sfullman

not really what I'm looking for:

field = "now is the time for all good men to come to the aid of their country"

select str_count("the", field)

//should return number 3

Lars_Berg

select 
(char_length(haystack) -
char_length(replace(haystack,needle,'')))/
char_length(needle) from t

tekky

if you are looking for a bit more info on the above post...

Posted by [name withheld] on March 1 2004 8:58am [Delete] [Edit]

Working on a project recently I needed the ability to count the number of occurrences of a given character in a given string. Unfortunately there is not a character counting function in MySQL, but the following suffices:

SELECT LENGTH(t1.string) - LENGTH(REPLACE(t1.string, 'f', '')) AS count
FROM table1 t1
WHERE string = 'foo';

This would then return a single result with a value of 1 (assuming of course there was only one record for 'foo'). Changing the replace argument from a 'f' to 'o' for example would give you again a single result but of course with a value of 2 since the character 'o' occours twice. Please note that this is case sensitive check; if you wish to search in a non case sensitive manner then simply wrap all string references in a LOWER().

Hope this helps.

from http://dev.mysql.com/doc/mysql/en/string-functions.html

sfullman

how limited mysql is in string handling - though the basics are there. I don't think there is any thing like php's preg_replace either. But that's a creative idea and I'll use it in the future. Obviously if you were looking for cat you'd find scat and catch as well without the regexp power.

I'm under the impression that there is no single query to a db that's going to yield organized search engine results, but rather that pre- adn post-processing are both required with php, and also the possible compliation of memory heap tables from the data. If anyone knows good articles on this let me know.

Sam

tomhath

If you really feel the need for putting that much logic into a query (I wouldn't suggest it) you can learn about stored procedures.

MySQL support for them is beginning in 5.0, but other databases have pretty much anything you can imagine:

http://www.developer.com/db/article.php/10920_3337411_1

http://www.devx.com/dotnet/Article/21286

And of course you can always download the source code for MySQL and hack it.

tekky

in regards to search engines, SQL is capable of it... you just have to design the search fields around FULL_TEXT and the associated queries that fit with it... atleast that is my opinion of it