RESOLVED Alright so I determined that NOPs Search box plainly sucks.....
- 304
- 1704
- 3/25/2010
- United States
If you have a fulltext index set on a column you can do this:
DECLARE @SearchWord nvarchar(30)
SET @SearchWord ='performance'
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, @SearchWord);
Microsoft MSDN describes CONTAINS as:
CONTAINS is a predicate used in a WHERE clause to search columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches.
In SQL Server, you can use four-part names in CONTAINS or FREETEXT full-text predicates to execute queries against linked servers.
CONTAINS can search for:
* A word or phrase.
* The prefix of a word or phrase.
* A word near another word.
* A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
* A word that is a synonym of another word using a thesaurus (for example, the word metal can have synonyms such as aluminum and steel).
Because "parameter sniffing" does not work across conversion, use nvarchar for better performance. In the example, declare @SearchWord as nvarchar(30).
DECLARE @SearchWord nvarchar(30)
SET @SearchWord ='performance'
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, @SearchWord);
Microsoft MSDN describes CONTAINS as:
CONTAINS is a predicate used in a WHERE clause to search columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches.
In SQL Server, you can use four-part names in CONTAINS or FREETEXT full-text predicates to execute queries against linked servers.
CONTAINS can search for:
* A word or phrase.
* The prefix of a word or phrase.
* A word near another word.
* A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
* A word that is a synonym of another word using a thesaurus (for example, the word metal can have synonyms such as aluminum and steel).
Because "parameter sniffing" does not work across conversion, use nvarchar for better performance. In the example, declare @SearchWord as nvarchar(30).
- 26
- 170
- 9/16/2010
- United States
mb wrote:
This is an easy change that makes a huge difference, thanks!
I was looking into how to swap the order of the first two words of the search term to help with words out of order, but did not come up with an easy solution. Has anyone found one? I am willing to limit to the first two words because after that there are n factorial combinations, and that would be too big a pain.
So, if the search term is shirt pocket green, all I need is a way to search for both shirt pocket green and pocket shirt green. Any ideas?
To allow for partial word matches in searches use the following instead:
.
SET @Keywords = REPLACE (@Keywords, ' ', '%')This will return more results because it doesn't use the spaces that acted as word boundaries, instead the keywords are surrounded by wildcards only.
.
This is an easy change that makes a huge difference, thanks!
I was looking into how to swap the order of the first two words of the search term to help with words out of order, but did not come up with an easy solution. Has anyone found one? I am willing to limit to the first two words because after that there are n factorial combinations, and that would be too big a pain.
So, if the search term is shirt pocket green, all I need is a way to search for both shirt pocket green and pocket shirt green. Any ideas?