CODEWITHSUNDEEP
sql-server-2005
galuvian
galuvian

Reputation: 499

Full Text Searching for single characters

I have a table with a TEXT column where the contents is just strings of CSV numbers. Example ",1,76,77,115," Each string can have an arbitrary number of numbers.

I am trying to set up Full Text Indexing so that I can search this column rapidly. This works great. Instead of running queries with

where MY_COL LIKE '%,77,%' and MY_COL LIKE '%,115,%'

I can do

where CONTAINS(MY_COL,'77 and 115')

However, when I try to search for a single character it doesn't work.

where CONTAINS(MY_COL,'1')

But I know that there should be records returned! I quickly found that I need to edit the Noise file and rebuild the index. But even after doing that it still doesn't work.

Upvotes: 1

Views: 527

Answers (3)

Brian Rudolph
Brian Rudolph

Reputation: 6318

Agreed. How does 12,15,33 not return that record for a search for 1 with fulltext? Use an actual table schema to accomplish this.

Upvotes: 0

alex
alex

Reputation: 5201

Working with relational databases that way is going to hurt.

Use a proper schema. Either store the values in different rows or use an array datatype for the column.

That will make solving the problem trivial.

Upvotes: 2

galuvian
galuvian

Reputation: 499

I fixed my own problem, although I'm not exactly sure what fixed it.

I dropped my table and populated a new one (my program does batch processing) and created a new Full Text Index. Maybe I wasn't being patient enough to allow the indexing to fully rebuild.

Upvotes: 0

Related Questions