how to full text search

Posted by Modit under Sql Server on 6/17/2012 | Points: 10 | Views : 946 | Status : [Member] | Replies : 3
I have a table called "users" that have four fields uid ,profession, country , city


create table users(uid int identity primary key,profession varchar(200),country varchar(200),city varchar(200))
go

insert into users(profession,category,country,city) values('doctor','india','lucknow')
insert into users(profession,country,city) values('mechanic','india','lucknow')
insert into users(profession,country,city) values('hawker','india','new delhi')
insert into users(profession,country,city) values('hawker','india','kanpur')
insert into users(profession,country,city) values('doctor','india','kanpur')



i have created a catalog , and want to search records using free text search.

Suppose a user have specified a value "Doctor in Lucknow" , it should firstly by doctor then by lucknow.

i have used the following query for this

SELECT f.rank, uid ,profession, category , country , city
FROM users,
CONTAINSTABLE(users, *, 'doctor or lucknow') f
WHERE users.uid=f.[key]
ORDER BY rank DESC;

it is returning me following output

48	1	doctor		india	lucknow
48 2 mechanic india lucknow
48 5 doctor india kanpur


but i want that record number 1 and 5 should come before record number 2.




Responses

Posted by: perfectchourasia-9163 on: 6/20/2012 [Member] Starter | Points: 25

Up
0
Down
http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/

ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://www.aspnetcodes.com/

Modit, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Modit on: 6/20/2012 [Member] Starter | Points: 25

Up
0
Down
thanks , but that link only explains the basics of full text

Modit, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muralidosscm on: 6/25/2012 [Member] Starter | Points: 25

Up
0
Down
SELECT f.rank, uid ,profession, category , country , city
FROM users,
CONTAINSTABLE(users, *, 'doctor or lucknow') f
WHERE users.uid=f.[key]
ORDER BY profession;

Regards
Muralidoss M

Modit, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response