July 9, 2010 at 4:59 am
Hello All,
I have one field in the database table having the value for eg as "Wilson,Gregory T".
In my SP there is one parameter @EmployeeName.When I pass the above exact string then it will returns me one record which is fine. Now I want to search this string in different conbinations i.e whatever I pass which is in the above string must return me one row of "Wilson,Gregory T".
So I have applied the full-text search and I am using the Contains function.
the best possible combinations of the above string from my side are as follows:
Wilson,Gregory T
Wiison Gregory
Gregory Wilson
Wilson T
Gregory T
T Wilson
T Gregory
Wilson,Gregory
Gregory,Wilson
Wilson,T
Gregory,T
T,Wilson
T,Gregory
Wilso, Gregory T
for eg I have tried the following:
SELECT *
FROM employeetest
WHERE Contains(employeename, 'Wilson and Gregory' );
It works fine.
This SP I am using in my ASP.Net code. So internally SP does not know what combination of string the user will pass to search the record.
So I want the simple solution that whatever combination the user passes from the front end the SP should manipulate it and returns the proper record i.e one record of "Wilson,Gregory T".
Could any one please let me know how is it possible with full text search ,it will be of great help.
Thanks
August 27, 2017 at 1:42 pm
I have problem with contains function, when i search with like '%ZAM%' operator, it finds all word that contains ZAM like ZAMANLAMA AZAMI ZAM and etc.. but when I use fts index contains function, it just find ZAM ZAMANLAMA but it doesnt find AZAMI or 123ZAM789. I have also tried CONTAINS (YourColumn, ' "ZAM" ' ) but it doesn't work. Please help me , fts is very fast but it could not find all contains like '%%' operator what should I do ?
August 28, 2017 at 1:11 pm
Computerengineerr - Sunday, August 27, 2017 1:42 PMÄ° have problem with contains function, when i search with like '%ZAM%' operator, it finds all word that contains ZAM like ZAMANLAMA AZAMI ZAM and etc.. but when I use fts index contains function, it just find ZAM ZAMANLAMA but it doesnt find AZAMI or 123ZAM789. I have also tried CONTAINS (YourColumn, ' "ZAM" ' ) but it doesn't work. Please help me , fts is very fast but it could not find all contains like '%%' operator what should I do ?
Not sure with SQL 2005 but in later versions, In full text search, the wildcard is not exactly a wildcard but you would use an asterisk rather than percent. You do need to put it in quotes - something like:
CONTAINS(YourColumn,' "ZAM*" ')
Refer to the prefix_term section in the Microsoft documentation:
CONTAINS (Transact-SQL)
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply