get the number of a certain word in a string

Forum Moderators: open

Message Too Old, No Replies

9:20 am on Nov 16, 2006 (gmt 0)

Hi, i want to search a word in a string and return the number of words that a string has.. but i dont know hot to do it..

example: i want to search for the word "word1" on this string:

"word1 test test test string2 word1 string3 word1"

The result should be 3 since there were 3 "word1" on that string..

Its like returning the number of words that that string has...

thanks.. appreciate.. =)

4:22 pm on Nov 16, 2006 (gmt 0)

Is this something you want to do in SQL (i.e. on multiple rows in a table), or just on a single row?

2:29 am on Nov 17, 2006 (gmt 0)

multiple rows...

5:06 am on Nov 17, 2006 (gmt 0)

A quick and dirty way would be something like

SELECT (LENGTH(yourfield)-LENGTH(REPLACE(yourfield,yourword,'')))/LENGTH(yourword)
FROM yourtable

It should be clear how this works - it also should be clear that it fails when "yourword" is a substring of another word.

There's probably a very elegant and more accurate method using regular expressions.