question

PenmetsaPP avatar image
PenmetsaPP asked

Issue of Full text search -- ContainsTable

Hi,

We have the full text search table and are using CONTAINSTABLE to search in the fulltext search column, its workingfine, but we have a requirement that we need to return results which match a particular sequence in word.

ex: The search text column has the word as 0203456-345567

if we give the input as " 0203456", the result is coming and showing the above column,

but if we search for " 203456" no results return,

Going through the net I found that we can perform search like "2034*" which will retun the result, ie we can add * at the end but can we have some command like "*2034", and this returning the results I could not get any such string even the FormOF is not giving me such output,

Can you please provide some way where I can use ContainsTable and also search for "*20345" string successfully.

sql-server-2008full-text
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
Rup avatar image
Rup answered

You will need to use Language 'Neutral' while building the Full Text Index. I have demonstrated below with the following example.

Create Table MyFTSearch
(
  id              int identity(1,1) not null,
  text_to_search  varchar(500)
);

Alter Table MyFTSearch Add Constraint MyFTSearch_PK Primary Key Clustered
(
  id
);

Insert Into MyFTSearch Values ('0203456-345567');
Insert Into MyFTSearch Values ('203456-345567');

Create Fulltext Index On MyFTSearch (text_to_search Language [Neutral])
Key Index MyFTSearch_PK;

Waitfor Delay '00:00:10'; -- Wait for the Catalog to populate

Now, issue the following SQL:

select *
from   MyFTSearch
inner join containstable( MyFTSearch, text_to_search, '203456') As KEY_TBL
  On MyFTSearch.id = KEY_TBL.[KEY];

You will get the following results:

id          text_to_search    
----------- ------------------
1           0203456-345567    
2           203456-345567     

Now, rebuild the index with language English like the following:

Drop Fulltext Index On MyFTSearch;

Create Fulltext Index On MyFTSearch (text_to_search Language [English])
Key Index MyFTSearch_PK;

Waitfor Delay '00:00:10';

If you run the same SQL again, you will get the following results:

id          text_to_search        
----------- ----------------------
2           203456-345567         

So, setting the Language to Neutral seems to address your problem. But you will need to be aware of the scnario when there are alphabetic characters in front of your numeric search criteria. Try the following:

Drop Fulltext Index On MyFTSearch;

Insert Into MyFTSearch Values ('XYZ-A203456-345567');

Create Fulltext Index On MyFTSearch (text_to_search Language [Neutral])
Key Index MyFTSearch_PK;

Waitfor Delay '00:00:10';

If you run the same query again, you will get the following results.

id          text_to_search        KEY         RANK
----------- --------------------- ----------- -----------
1           0203456-345567        1           16
2           203456-345567         2           32
3           XYZ-A203456-345567    3           32

I have added the RANK column to the output to show that not only do the value "XYZ-A203456-345567" gets selected it actually gets a higher rank than "0203456-345567".

Hope this will not be a stumbling block for you.

10 |1200 characters needed characters left characters exceeded

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.