| Author |
 Topic  |
|
|
Hobbsy2011
Starting Member
United Kingdom
5 Posts |
 Posted - 10/19/2012 : 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
|
Edited by - Hobbsy2011 on 10/19/2012 05:54:08
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 10/19/2012 : 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 |
 |
|
|
Hobbsy2011
Starting Member
United Kingdom
5 Posts |
Posted - 10/19/2012 : 09:40:43
|
Thank you Chandu, that will do the job nicely!!
Regards,
Dave |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 10/22/2012 : 03:28:52
|
Welcome
--
Chandu |
|
|
| |
Topic |
|
|
|
Counting the number of instances of a string
