SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Counting the number of instances of a string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hobbsy2011
Starting Member

United Kingdom
5 Posts

Posted - 10/19/2012 :  05:32:18  Show Profile  Reply with Quote
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









Edited by - Hobbsy2011 on 10/19/2012 05:54:08

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 10/19/2012 :  08:00:51  Show Profile  Reply with Quote

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
Go to Top of Page

Hobbsy2011
Starting Member

United Kingdom
5 Posts

Posted - 10/19/2012 :  09:40:43  Show Profile  Reply with Quote
Thank you Chandu, that will do the job nicely!!

Regards,

Dave
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 10/22/2012 :  03:28:52  Show Profile  Reply with Quote
Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000