sql full text search like google search

Question

sql full text search like google search

a2zwd

SSCrazy

Points: 2347
More actions
March 26, 2009 at 9:18 am

#130223

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37299 More actions · Answer 1

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

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

a2zwd SSCrazy Points: 2347 More actions · Answer 2

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

Artur Sokhikyan SSC Eights! Points: 900 More actions · Answer 3

a2zwd (3/26/2009)
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

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...