question

jasmy avatar image
jasmy asked

Exact word match in Sql 2012

We need to get exact match of words from two or more tables with out using like clause
querysql-server-bug
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image TimothyAWiseman commented ·
Just out of curiosity, why would you avoid like? That seems the natural approach if you are trying to find instances of a substring.
0 Likes 0 ·
jasmy avatar image jasmy commented ·
Hi TimothyAWiseman,Grant Fritchey "Like" doesn't take Index. We have an email table with millions of records and we need to search the subject and get the results in seconds . We tried containstable but if there is no matching records it will take time. ,Hi TimothyAWiseman Like doesn't take index. We have the table with millions of records. and need to fetch data in seconds.We tried containstable but if there is no match it takes more time.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
I'm assuming you mean an exact match within a string of words, otherwise you just use equals. Since you can't/won't use LIKE, then you need to create a [full text index][1] and use that. [1]: http://technet.microsoft.com/en-us/library/ms142571.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Venkataraman avatar image
Venkataraman answered
You can consider union of SELECTS instead of OR condition for multiple words checking. For Best Practices of Full text Index refer to http://technet.microsoft.com/en-us/library/aa175787(v=sql.80).aspx Also consider returning keys first and then using the keys to retrieve the rows from table. http://stackoverflow.com/questions/2750870/sql-serve-full-text-search-with-containstable-is-very-slow-when-used-in-join Try to use NVARCHAR. http://social.msdn.microsoft.com/Forums/sqlserver/en-US/eb83f59d-0885-49d5-aa56-38ee132c62e7/full-text-search-is-slow?forum=transactsql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
If you can't use standard indexes and Full Text doesn't work, then you have no alternative but to go to a third party tool and load your data differently. Google sells a tool and a service for doing this kind of thing. Microsoft has one too incorporated into SharePoint [called Fast][1]. There are a [ton of others][2]. [1]: http://support.microsoft.com/ph/15080?wa=wsignin1.0 [2]: http://full-text-search.findthebest.com/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.