Search Multiple keywords across multiple fields



  • 1. Using a cube to make an OLAP clustering prediction
    I have a trained clustering model based on a cube with two dimensions. The training produced clusters of the case dimension (A) elements, based on the existence of records in the cube that associate (via foreign keys) a record from A with a record from dimension B (I am not interested in the other columns of the associations table). How can I define a query that receives a series of record asociations (i.e. (A1,B3), (A1,B2), (A1,B5), (A2,B1)...) and produces a list of the elements and their clusters: Element Cluster A1 4 A2 7 ... I tried with the following query but it didn't work (it says that [B.B Code] doesn't exist): SELECT [N].[A_code], Cluster() AS clust FROM [ClusterModel] PREDICTION JOIN OPENROWSET('SQLOLEDB','localhost';'sa';'123', 'SELECT * FROM DB1.dbo.Associations') as [N] ON [ClusterModel].[A.A Code] = [N].[A_code] AND [ClusterModel].[B.B Code] = [N].[B_code]
  • 2. Data Mining algortihms
    i dont know if this is the right group ,therefore sorry for any inconvenience. i didn't work on data mining before,now i want to implement an algortihm as a beginning. When i search through the net, i see lots of algorithms. My aim is to find relations between data in a database. but i dont know where should i begin. Which algorithms should i use? thanks in advance..
  • 3. Probablity function in SQL Server. HELP!!!
    I need a function equivalent to the excel CHIDIST function. This function calculates the one-tailed probability of the chi-squared distribution. I don't think there is a built in function for it in SQL server, so would I calculate it? Is there a third-party tool that can do this? Does anyone know the formulae for it? Maybe I can hand-code it. Thanks for any help. -Emad
  • 4. ClusterDistance
    Does anybody knows why the ClusterDistance() function doesn't produce an output when used in a clustering prediction query? The functions Cluster() and ClusterProbability work OK.

Search Multiple keywords across multiple fields

Postby Darren Heinrich » Fri, 06 Feb 2004 06:50:30 GMT


I'm about halfway through building a search engine using ASP, SQL and
As part of that search engine I need to search multiple keywords across
multiple fields
So far I've figured out how to split a string into words. From here I could:

Construct an SQL statement of varying length that, using the LIKE and AND
operators. This seems damn hard!

Alternately I could concatenate the multiple fields into a variable or array
then search.

What is best - can anyone provide examples ?


Re: Search Multiple keywords across multiple fields

Postby John Kane » Sun, 15 Feb 2004 03:24:15 GMT

Depending upon what you're trying to do, perhaps T-SQL LIKE is not the best
approach for you. T-SQL LIKE is best used for pattern / string search, while
SQL Full-Text Search (FTS) is designed for language-specific word search. If
you're using SQL Server 7.0 or SQL Server 2000, search for "full text"
(using the double quotes) under the search tab in Books Online (BOL) for
more details.

FYI, you can also post FTS related questions to the newsgroup:


Re: Search Multiple keywords across multiple fields

Postby Darren Heinrich » Sun, 15 Feb 2004 09:20:39 GMT

Thanks for your reply, but unfortunately I'm using access.

Maybe I should have explained it like this:
In one record in my db I have:

field1 = "the dog"
field2  = "the cat"
field3 = "the bird"

I need a query that will find the above record when the user enters the
keywords of "dog cat bird"
so far my current page works well but it looks for "dog cat bird" in one
field, then the next fieldetc.

Have a look:  http://www.**--****.com/ 

any ideas ?


*** Sent via Developersdex  http://www.**--****.com/  ***
Don't just participate in USENET...get rewarded for it!

Re: Search Multiple keywords across multiple fields

Postby John Kane » Sun, 15 Feb 2004 11:34:48 GMT

You're welcome, Darren,
When you said SQL, I assumed you meant SQL Server. I did check out your site
and selected boy and all fields, and got the following results:

You searched for boy as a phrase
Select tblProducts.productID, tblProducts.productName,
tblProducts.longDescription, tblProducts.description,
tblManufacturers.manufacturerName FROM tblManufacturers INNER JOIN
tblProducts ON tblManufacturers.manufacturerId = tblProducts.manufacturerId
WHERE tblProducts.productName Like '%boy%' OR
              tblProducts.longDescription Like '%boy%' OR
              tblProducts.description Like '%boy%' OR
              tblManufacturers.manufacturerName Like '%boy%'
ORDER BY productID

1 | 1146 | Les Paul Studio Gothic | Take a walk on the dark side | Epiphone
2 | 1253 | Smallstone | The most popular phase shifter of all time |

I'm assuming that boy is in the portion of the description field that is not
displayed above...

Yes, I do have a solution that *might* be appropriate for Access, as it will
certainly work for MSDE. However, this is not a discussion to have in the
datamining newsgroup. Please, email me directly and we can discuss this


Similar Threads:

1.searching across multiple fields

I have a query designed to search multiple fields for any word that
starts with the characters entered in a search. Each field will
typically contain more than one word. The query takes the form:

field1, field2
FROM table1 WHERE ((field1 LIKE "a%") OR (field1 LIKE "% a%") OR (
field2 LIKE "a%") OR (field2 LIKE "% a%"))

Is there a more efficient way to search than this?


2.Multiple fields with multiple search items

Hello everyone:  I am having a bit of a problem trying to wrap my head
around a particular select statement... I have a table that has
numerous stores.  Beyond the normal address, city, state, zip fields,
each store record has 5 fields, 1 for each for ice cream flavor
carried at that particular store.  I have a search box that lets you
search for ice cream flavors.  Right now searching for one flavor is
hard enough since my select statement select all records "where
flavor1 = "x" or flavor2 = 'x' or...", but I would like the ability to
search for multiple flavors, and have the returned results start with
those stores that match the most flavors searched upon.  Does anyone
have any way of doing this?  Also, the page is written in PHP so if
you have any php related way to handle this issue, I am more than
happy to try them as well.  Thank you!!!

3.Multiple Keyword Search using SQL PROC

I need to support multiple keyword search, not boolean searchs, just simple 
searches, i.e. "marhsall ma". How is this done? Do I send the entire search 
string to the Proc? How do I deal with it there? I'm thinking I need to 
build the WHERE clause in the code behind and send it to the proc. What do 
you think?


David Lozzi 

4.Full-text search across multiple tables


We are developing an application against an MS SQL Server 2000 database
which requires that we implement full-text searching across columns in
multiple tables.  The research that we have conducted seems to indicate
that this is not directly possible within SQL Server 2000.  And we can
find no way to implement this as the catalogs that are generated are
table specific.

As a work-around, we are planning to create a secondary table used
strictly for searching which is going to denormalize and combine the
searchable data into one catalog.  We just want to confirm that this is
the recommended approach or if there is an alternate solution anyone
has used.  Please note that upgrading to SQL Server 2005 is not an


John Fleming

5.Full Text Search across multiple tabels in SQL 2005/2008

We have a requirement where we need to do full text search for a given string 
across multiple unrelated tables in the DB.
The number of tables is quite large, and hence we can't use a UNION of 
results on all tables. Also, this won't give us the right ranking.

The one option that I could think of involves creating a new de-normalized 
table containing the columns from tables we want to index(with other related 
data). Then I can create a Full Text Index on columns of this table, and 
perform search queries on this. But this would mean, that I'll have the 
overhead of maintaining this table in sync with creates and updates on the 
tables involved.

Is there any other way to accomplish this in SQL 2005/2008? 

6. Full Text Search across multiple tables (Parent-Child)

7. Search across multiple columns

8. searching across multiple columns



Who is online

Users browsing this forum: No registered users and 50 guest