full text search query a piece of word

Question

0.00/5 (No votes)

See more:

Example
I want to search 'abc' in my table.
My table contains below words
abcde
eabcd
deabc

My query is like that but I can't get any result

create procedure searchFunction @keyword nvarchar(50)
AS
BEGIN
SET @keyword='"*'+@keyword+'*"'
SELECT * FROM table WHERE CONTAINS(words,@keyword)
END

EXEC searchFunction @keyword='abc'

Posted 26-Mar-15 3:05am

amagitech

Updated 26-Mar-15 3:08am

v2

Add a Solution

Comments

Sascha Lefèvre 26-Mar-15 9:38am

As far as I know, SQL-Server FTI doesn't allow left-side-truncation. Try removing the left asterisk and see if it finds "abcde". If you need more full-fledged FTI-functionality (including left-side-truncation), I'd recommend Lucene.Net.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Accepted Answer · 2015-03-26T03:19:00

Solution 1

If you do not need the full text search extended features then use the Like operator:

Sql uses '%' as a full wildcard, not '*'.

More info on SQL wildcards here:

http://www.w3schools.com/sql/sql_wildcards.asp[^]

If you must use the full text search then there are some things to note about CONTAINS:

If you wish to search "starts with" then use CONTAINS('*'+term)
If you wish to search for any contains then use CONTAINS(term)

More on this here:
https://msdn.microsoft.com/en-us/library/ms187787.aspx[^]

Posted 26-Mar-15 3:19am

Andy Lanng

Updated 26-Mar-15 3:43am

v3

Comments

amagitech 26-Mar-15 9:26am

https://msdn.microsoft.com/en-us/library/ms142583.aspx
There is some example about full text search.
But I can use @keyword='"'+@keyword+'*"' this type but this isn't meet my exceptions. This type search only 'abcd','abce'

Andy Lanng 26-Mar-15 9:36am

oic - ur using full text search. You don't need to. A simple where 'abcde' LIKE '%abc%' would work.

If you use contains then omit the '*'s. These are not required if doing a full text search.

Punctuation is ignored. "abc def" matches "abc. def".

Unless this is highly desired, Use the Like statement. It should be more efficient

Sascha Lefèvre 26-Mar-15 9:39am

Fulltext-Index is a different story, Andy.

Andy Lanng 26-Mar-15 9:42am

Now I know - just trying to cover my tracks now
Solution Updated

Sascha Lefèvre 26-Mar-15 9:45am

;-)

amagitech 26-Mar-15 10:03am

So that mean I can't use full text search like '%keyword%'.
I can use it only

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").

Andy Lanng 26-Mar-15 10:07am

That is my understanding but I will defer to others. Would you like me to delete my solution, or is it helpful.

amagitech 26-Mar-15 10:19am

Thanks my friend. Your solution is help to save my time :)

Andy Lanng 26-Mar-15 10:24am

Thanks for rating ^_^