1

I am quering a table (about 150,000 rows and growing) with a big varchar field (size 2000) which can't be indexed (and there's no point even if it could be). I am using Sql Server 2008. The query I used till now was:

select * from tbl_name where field_name like '%bla bla%'

("bla bla" is according to what the user searched for)

In order to improve performence, I wann'a start using the Full-Text Search feature (already defined a catalog and a text index on this field). I am a bit confused from what I read about quering with this option. what query should I use in order to get exactly the same results as the query I used to use before?

  • Comments:
    1. I would like to get results which are not case sensative, as it worked before (meaning if the user searches for "LG" he will also get results that contains "Lg").
    2. If user enters "Sams" he will also get "Samsung".

Thanks! Eran.

Eran
  • 11
  • 4
  • 1
    what do you mean by full `select * from tbl_name where field_name='users text` ???? – NullPoiиteя Nov 18 '12 at 17:23
  • http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/ – JohnLBevan Nov 18 '12 at 17:23
  • "users text" is just an example - I query by the string that the user puts in the search box – Eran Nov 18 '12 at 17:35
  • Martin - do you mean "Accent sensative" = false" ? – Eran Nov 18 '12 at 17:36
  • I want to query by the whole string that the user has entered, as one phrase. The CONTAINS query doesn't returns the results I need even with one word. Lets say I have 5 rows in the DB: 1. ggg Eran fff 2. hhhEranttt 3. Eranggg 4. hhhhh Eran 5. rrrggg Eran fffhhh and I query by: SELECT * FROM tbl_name WHERE CONTAINS(field_name, '"eran"'); I only get rows 1,4,5 as the results, but I need to get them all. So, there is no way to use the free text index instead of quering by WHERE field_name LIKE '%[search_text]%' ? – Eran Nov 19 '12 at 09:18
  • Hopefully you've read the documentation for the [`CONTAINS()`](http://msdn.microsoft.com/en-us/library/ms187787(v=sql.100).aspx) function: it explains how to search for whole words and the start of words. What exactly are you asking that is not covered in the documentation? Case sensitivity is a matter of the collation, but you can use the [`COLLATE`](http://msdn.microsoft.com/en-us/library/ms184391.aspx) keyword to force a case-insensitive search if required. – Pondlife Nov 19 '12 at 16:40

1 Answers1

2

CONTAINS() will get you the LIKE() functionality you are seeking with one exception - I noticed in the comments that you also want to match the second entry - "hhhEranttt". Unfortunately, due to the lack of suffix search this is currently not possible.

For the other entries you can run a prefix search - CONTAINS(field_name, '"eran*"') which matches all the other entries since full-text searches are case-insensitive.

HTH.

aks
  • 24,359
  • 3
  • 32
  • 35