?
Solved

Search by any word!

Posted on 1998-07-20
2
Medium Priority
?
215 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 2

Accepted Solution

by:
odessa earned 100 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

764 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