RE: Windows SQL 200X Full Text search query quandry [spamtrap heur]
Paul,
SUBSTRING(URLdescription,PATINDEX('#Searchstring#',URLdescription),256)
That worked perfectly. I had never used the PATINDEX command before so I
overlooked it completely.
A little massaging of the search string to make it a wildcard matching
pattern and all is well. Thank you.
Dennis Powers
UXB Internet - A Website Design Hosting Company
P.O. Box 6028
Wolcott, CT 06716
203-879-2844
http://www.uxbinternet.com
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334975
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Windows SQL 200X Full Text search query quandry [spamtrap heur]
On 7/1/2010 2:21 AM, UXB Internet wrote: That worked perfectly. I had never used the PATINDEX command before so I overlooked it completely. books-on-line is your best friend when it comes to sql server. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335004 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Windows SQL 200X Full Text search query quandry
I on a project we are using the SQL 200X internal Full Text catalog to search the database and it is finding the correct data just fine. However, the client would like me to pull a summary of the data that matched the text search out of the database. Just the summary of the section of the data that matched. I am unsure if I can do this. There are two ntext fields that that may contain upwards of 8K in text data that we are searching. My question is how do I construct a search query to get a 256bte section of the ntext field that contains the matching criteria? The existing Query consists of several joined queries with different weights each excluding the data set from the previous query. To keep it simple, Here is a sample of one section of several joined queries. SELECT (KEY_TBL.RANK * 1) AS RANK, ID ,URL, URLName, URLTitle, URLRating, URLImage, CAST(URLDescription As nvarchar(256)) AS URLDEsc, CAST( URLKeywords As nvarchar(256)) AS Keywords FROM URLs AS URLTable INNER JOIN FREETEXTTABLE (URLs, URLdescription ,'#Searchstring#', 20 ) AS KEY_TBL ON URLTable.ID = KEY_TBL.[KEY] How would I pull just the 256 byte section of the ntext field URLdescription that matches the searchstring? Dennis Powers UXB Internet - A Website Design Hosting Company P.O. Box 6028 Wolcott, CT 06716 203-879-2844 http://www.uxbinternet.com ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334941 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Windows SQL 200X Full Text search query quandry [spamtrap heur]
On 6/30/2010 5:50 AM, UXB Internet wrote:
How would I pull just the 256 byte section of the ntext field URLdescription
that matches the searchstring?
one way might be to use PATINDEX SUBSTRING (see sql server books-on-line for
details).
SUBSTRING(URLdescription,PATINDEX('#Searchstring#',URLdescription),256)
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334948
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
