query text field for certain words 1

[jmk418]

I have a text field in a table that I want to search through for the word "lesion". The word may not be in the text field at all or may be in there once, twice, three times or more. The format for the text field is: "LVEF - 60%, Right Dominant, DIAG1 Generic 80% lesion, LAD Generic 80% lesion, RCA (Proximal), Eccentric 95% lesion, RT PDA Generic 90% lesion". What I want to be able to do is search through the text field and count the number of times lesion appears. I've used charindex and substring to pull other words out of text fields but I'm not sure how to go about counting the number of times a certain word appears. Any help is greatly appreciated.
Thanks in advnace.
Jeremy

 

[SQLDenis]

DECLARE @chvString VARCHAR(500)
SELECT @chvString ='LVEF - 60%, Right Dominant, DIAG1 Generic 80% lesion, LAD Generic 80% lesion, RCA (Proximal), Eccentric 95% lesion, RT PDA Generic 90% lesion'

DECLARE @chvSearchString VARCHAR(50)
SELECT @chvSearchString = 'lesion'

SELECT LEN(@chvString) AS StringLength,
LEN(@chvSearchString) AS SearchForStringLength,(LEN(@chvString)-
(LEN(REPLACE(@chvString,@chvSearchString,''))))/LEN(@chvSearchString) AS HowManyOccurances

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jmk418]

Thanks. That is exactly what I was looking for.