Full text search for Numeric Values...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Full text search for Numeric Values...

Author

Topic

MacJK
Starting Member

24 Posts

Posted - 2011-05-13 : 11:54:41

Hi,

i create a Full Text Search for a Account Table.

All Columns for the Full Text are NVARCHAR(100) if i search for a City ore part of the Account Name i get all results. But if i search for a part Account Number i get no results.

Did i have a wrong query or what is the point of this?

SELECT [AccountUI] ,
       [AccountName] AS Name ,
       [AccountNumber] ,
       [Address1_PostalCode] ,
       [Address1City] ,
       [Telephone1] ,
       [CurrencyID] ,
       [Address1_Line1] ,
       [Address1_Line2] ,
  FROM [udt_Account]
 WHERE CONTAINS((AccountName, AccountNumber, Address1_City, Address1_PostalCode, Telephone1), ' "*100666*" ')

Thanks for hints and Help.

br
Jaroslaw

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-13 : 12:06:19

Full text indexing and search is intended for searching words and word forms, not numeric data. If you want to search account numbers as you describe, use the LIKE operator (see Books Online for details).

Additionally, if you know someone is searching for an account number, there's no point searching the other columns for it.

MacJK
Starting Member

24 Posts

Posted - 2011-05-13 : 12:12:02

Hi robvolk,

thanks for fast response. I change the query.

That's the reason for the full Text Search it is only one search filed for a Search over all columns to find the customer. I can't say it is the account number it could be also the Zip code or Phone Number.

br
Jaroslaw

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-13 : 12:31:10

The same will apply to any numeric data, that's excluded from full-text search. I'm not sure if you can configure it to index numbers at all, and even then it won't match partial numbers the way you want.

MacJK
Starting Member

24 Posts

Posted - 2011-05-14 : 05:03:25

Hi robvolk,

ok but my problem was speed, i have about 150.000 records in the Table.

The best Execution time i get with a like over all fields is about 500ms.

If i use the fulltext it take about 5ms. If i compare fulltext and LIKE for numbers i get about 250ms.

Are the 500ms for only like per colums a realistic value or is this to long?

The result are about 15 for this query.

br
Jaroslaw

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-14 : 08:13:15

That's not only realistic but pretty good if you ask me. I have a feeling that's due to data caching, and would expect it to be slower with larger sets or an empty data cache.

imgharavi
Starting Member

5 Posts

Posted - 2011-05-19 : 08:51:18

I have the same issue. i forced to use FTS insted of like for my account table and my users very regualrly using a part of phone number for their search and in FTS its not working for example contains(*,'09255389') return value but contains(*,'092553*') does not return any value.

Any help?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources