March 26, 2009 at 9:18 am
Hi,
I have a table in my database with the following fields.
Location INT (FK)
Specialty INT(FK)
Sex VARCHAR(1)
Availability VARCHAR [ 1-Monday, 2-Tue] in the format (1, 5, 6)
DocumentFilename VARCHAR
I want to do full text search. From the front-end, user will type search textbox as
London, ENT, male, thursday
The search should do on Location = London, Specialty = ENT, Sex = M, Availability = 4
How can I do this? Its like google search.
thanks in advance
March 26, 2009 at 10:39 am
a2zwd (3/26/2009)
Hi,I have a table in my database with the following fields.
Location INT (FK)
Specialty INT(FK)
Sex VARCHAR(1)
Availability VARCHAR [ 1-Monday, 2-Tue] in the format (1, 5, 6)
DocumentFilename VARCHAR
I want to do full text search. From the front-end, user will type search textbox as
London, ENT, male, thursday
The search should do on Location = London, Specialty = ENT, Sex = M, Availability = 4
How can I do this? Its like google search.
thanks in advance
Hi
I don't understand your question completely, but:
* Translate male/female to M/W with an IF or CASE clause
* Translate Thursday to 4
* Build a SELECT statement with the specified parameters.
For a more google like search you should investigate the full-text index feature of SQL Server.
Greets
Flo
March 26, 2009 at 10:55 am
Hi
Thanks for the reply.
I have created the full text index on the table.
When I issued a query like
select * from tablename where contains(*,'Lon')
Its not retrieving anything. When I type London, I am getting the results.
If i use WHERE CONTAINS(*,'M') its not searching sex column(its involved in full text index).
Whats the problem.
cheres
March 30, 2009 at 6:19 am
a2zwd (3/26/2009)
HiThanks for the reply.
I have created the full text index on the table.
When I issued a query like
select * from tablename where contains(*,'Lon')
Its not retrieving anything. When I type London, I am getting the results.
If i use WHERE CONTAINS(*,'M') its not searching sex column(its involved in full text index).
Whats the problem.
cheres
1) use select * from tablename where contains(*,'"Lon*"') .... The word london will be included in results.
2) There are some string expressions that are ignored during the Full text search. they are called "Noise words"
Look at the files located in "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData". Here you can find the lists of noise words. 'M' is a noise word by default. Thats why you are not getting any result for WHERE CONTAINS(*,'M').
Why do you want to make a "Full Text" search? I think this is a case where full text search is not acceptable.
All you have to do is convert the search query string specified by user into 4 parameters and filter the table by
"WHERE Location like '%' + @parameterLocation + '%' AND Specialty = @parameterSpecialty AND Sex = @parameterSex AND Availability like '%' + @parameterAvailability + '%'
-------------------------
- Name?
- Abu Dalah Sarafi.
- Sex?
- 3 times a week!
- No, no. Male or Female?
- Male, female, sometimes camel...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply