searching database

[deepuv04]

Hi,
I have a table Users with the columns say
UserName
FirstName
LastName
City
State
Province
Zip
Country
Location
Title
Gender
GradeLevel
Manager
Ethnicity
Department
Division
Experience
Education
TimeZone etc...

Here my requirement is implementing the search functionality which means, user can type any thing in the search box. if the enter text is matches with any of the above columns that record must be displayed on the front end.

can anybody give me the best approach to implement this functionality, another thing is there will be 100000 + records in the table.


Thanks

[debasisdas]

is that a requirment or you are asking out of curiosity ? it seems you got the idea from search option of some of the online networking sites.

[deepuv04]

Hi,
This is the requirement I have to implement.
Actually I am trying with the following query but I want a better approach to implement this functionality.

DECLARE @Str varchar(100)
 
SET @Str = 'Jessica'
 
SELECT  *
FROM   Users
WHERE  
       ( (@Str) like ('%' + FirstName + '%') AND (FirstName is not null) )OR
       ( (@Str) like ('%' + TimeZone + '%') AND (TimeZone is not null) )
 

Thanks

[ck9663]

I heard of FULL-TEXT search but have not really used it yet. Is that something you can use?

-- CK