May 30, 2011 at 7:05 am
Hi,
How can I use full text to perform "Starts with" search with FTI that will search only the first word in data.
Details:
I have a Users table (UserID int, Name varchar(1000)).
There is a FTI on this table.
Say the user's name is "John Smith".
I want to implement a logic that when user enters only 1 symbol then I will return only the Users for which Name column data starts with that symbol (like LEFT(Name, 1) = 'J').
Now when I use CONTAINS(Name, '"S*"') "John Smith" is also returned, but it shouldn't.
Thanks in advance.
-------------------------
- Name?
- Abu Dalah Sarafi.
- Sex?
- 3 times a week!
- No, no. Male or Female?
- Male, female, sometimes camel...
May 30, 2011 at 7:22 am
Any reason why you have to do it with FTS?
WHERE Name LIKE 'S%' sounds like a reasonable alternative to me!
And no I don't know how to do that in FTS!
May 30, 2011 at 7:51 am
I don't want to use LIKE because the table contains more than 2 million rows. It makes the query to run very slow.
-------------------------
- Name?
- Abu Dalah Sarafi.
- Sex?
- 3 times a week!
- No, no. Male or Female?
- Male, female, sometimes camel...
May 30, 2011 at 7:55 am
Like I said I'm not a FTS guru but I know enough to have a real good guess that FTS can't really fare any better than like 'S%'
What's the full query? Do you have an index on that column?
Please post the actual execution plan so we can have a look at it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply