MSSQL full-text search limitations

Posted on 2008-06-16
Medium Priority
Last Modified: 2008-06-23
Hi Experts,
I have 3 tables:
tblMaps - ID, Map
tblLocations - ID, Location
tblMap_Location - ID, MapID, LocationID

Each Map have between 1000-3000 Locations. Each Location is limited to 100 characters in length. tblMaps is expected to have between 2 to 2.5 million records.

I'm currently using full-text indexing on tblLocations.

I found that the CONTAINS statement has some limitations. It is unable to perform wildcard search such as: *ork, Y*k or *or*. I would have to use LIKE statement for those searches.

My question are:
1) Is there a way to perform the wildcard searches that I specified using CONTAINS statemnt?
2) Does the LIKE statement benefit from full-text indexing?

Question by:noobe1
  • 2
LVL 43

Expert Comment

by:Eugene Z
ID: 21796516
SQL Server does not
support a leading wildcard in full text searches
Try the * after your search criteria  
select * from yourTablename where contains(yourcolumnname,'Y*"')  
also check:
all idea of FTS: do not use  "like"


Author Comment

ID: 21799283

I know that using wildcard at the end of the search criteria works, but that really limits the search results.

LVL 43

Accepted Solution

Eugene Z earned 1500 total points
ID: 21802493

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question