Counting the number of instances of a string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Counting the number of instances of a string

Author

Topic

Hobbsy2011
Starting Member

5 Posts

Posted - 2012-10-19 : 05:32:18

Hi,

I have a table in the database and one of the columns contains a blob of XML data. I want to build a search function to enable users to search the column for a specific string.

So far I have...

With SearchResults as (
SELECT Personnel_CVs_ParsedData.RecordID, Personnel_CVs_ParsedData.PersonID, Personnel_CVs_ParsedData.CV_ID,
Personnel_Master.Salutation, Personnel_Master.FirstName,
Personnel_Master.LastName, Personnel_Master.Town, Personnel_Master.PostCode, Personnel_Master.CurrentlyWorkCompany, Personnel_Master.RegistrationDate
FROM Personnel_CVs_ParsedData INNER JOIN
Personnel_Master ON Personnel_CVs_ParsedData.PersonID = Personnel_Master.PersonID
WHERE CONTAINS(Personnel_CVs_ParsedData.XmlData, '"Recruitment*" NEAR "Consultant*"')
)

SELECT DISTINCT PersonID, Salutation, FirstName, LastName, Town, PostCode, CurrentlyWorkCompany, RegistrationDate FROM SearchResults

I'm no SQL expert, but the above seems to do the job & gets me a unique list of candidates with the supplied term.

What I would really like to achieve is to add another column to the end of the table & order the results based on the value in that column.

The value in that column would be the total number of times the given phrase was found within the string. I could then order the list in descending order, so users would be looking at the most relevant records first.

Just to clarify my problem a bit better, I would like to count the number of times a search term is found within the string, then add the TotalCount column to the end of my result set.

Any ideas greatly appreciated as i'm struggling with it.

Thanks

Dave

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-19 : 08:00:51

To find out the number of occurrences of a phrase

DECLARE @search varchar(100) = 'town small'
DECLARE @string Varchar(100) = 'world lonely a in living girl town small a just girl town small a just in living girl'
SELECT (len(@string)- len(replace(@string, @search, '')))/ LEN(@search), LEN(@string)

--
Chandu