Clever Geek Handbook

Full text Search for single characters

I have a table with a TEXT column where the content is just strings of CSV numbers. Example ", 1,76,77,115," Each line can have any number of numbers.

I am trying to set up full text indexing to quickly find this column. This works great. Instead of executing 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 find one character it doesn't work.

where CONTAINS(MY_COL,'1')

But I know the records must be returned! I quickly discovered that I needed to edit the Noise file and rebuild the index. But even after that it still doesn't work.

+1


source to share


3 answers


Working with relational databases will be painful.

Use the correct scheme. Either store the values ​​in different rows, or use the array datatype for the column.



This will trivially solve the problem.

+2


source


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



I dropped the table and filled in a new one (my program is doing batch processing) and created a new Full Text Index. I may not have been patient enough to let the indexing rebuild completely.

0


source


I agree. How does 12,15,33 not return this full text search 1 record? Use the actual table schema to achieve this.

0


source






More articles:


All Articles