Returning How Many Times a Character Occurs in a String

I need to know how to figure out the number of times a character occurs within a string, in SQL.

For example, "happy" concerning "p" would be evaluated to 2.This is generic but you could taylor it:

DECLARE @.MyString NVARCHAR(20)

SET @.MyString = 'Happy'

SELECT LEN(@.MyString) - LEN(REPLACE(@.MyString, 'p', ''))

Basically you are asking for the length of the string (5) minus the length of the string if there were no 'p' characters in it (3). The difference is the number of 'p' characters.

Hope that helps,
Ian|||^ That one is awesome. lol very nice.