Solved

Search by any word!

Posted on 1998-07-20
2
212 Views
Last Modified: 2010-03-19
I want to know how to write the SQLQuery that it can search in the table by any word!
0
Comment
Question by:ruisun
2 Comments
 
LVL 2

Accepted Solution

by:
odessa earned 50 total points
ID: 1091801
very dificult question if you want to search whole table for a word (for example "KILE") do some like this:
select * from TABLE
where
(FILED1 like "%KILE%") or
(FILED2 like "%KILE%") or
(FILED3 like "%KILE%") ...


. some like this this is the only way
0
 
LVL 1

Expert Comment

by:mativare
ID: 1091802
In MSSQL server 7.0 beta you can use full text indexing with
Contains
Contains is powerful keyword
This example searches for all products with words of the form "dry": dried, drying, and so on.
(See if you use like dry* over here, it is another story and it returns lot of garbage too)
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' FORMSOF (INFLECTIONAL, dry) ')
GO
I cannot do it with 'like'
also it can use Near() function, wich searches word next to given word
[WHERE CONTAINS(ProductName, 'spread NEAR () Boysenberry']
also uses weighted terms
WHERE CONTAINS(Description, 'ISABOUT (spread weight (.8),
    sauces weight (.4), relishes weight (.2) )' )
See it is simple and powerful, it scrolls every row but
with full text indexing it allows to do sophisticated searches fast, before it was possible only in DB2 and Oracle.
I cannot see we can replace all this examples with LIKE
More in MSSQL 7.0 beta Books Online


0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question