mercredi 2 mars 2016

Search MS SQL Database and get part of of text matching with the kewords

I am using simple search sql to search the table for matching search keyword or string. For example i have a News table and i need to get 10 words around the matched keyword.

NewsID Title      Detail
1      xxxx       <p> Hello How are you! is this the word you are looking for.</p>
2      yyyy       <p> this is the detail of second news of today. </p>

right now i use simple select statement to search

Select * FROM News WHERE Detail Like '%word%'

This matched with first row.

Now i need to get the two or three words before & after the matching keyword like

Is this the word you are.

Not sure how this can be don't using MS SQL function or CTE query.

We can do this using C# & Regex but this will be too heavy as search has to go through thousands of records and Details field of news column can have up to 1000 words or more for each news item.

Just want to mention i don't have FreeText search option on the server

Appreciate pointer to resolve this in best & optimized way

Aucun commentaire:

Enregistrer un commentaire