?
Solved

Searching two database columns for similarities

Posted on 2005-04-21
10
Medium Priority
?
191 Views
Last Modified: 2010-03-19
Hi Experts,

Quite simply, I'm looking for a way to compare a particular column in Table 1 with a particular column in Table 2.

No, I'm not crazy: here's a better way of explaining it:

Table 1 contains knowledgebase articles. The column I want to use as my search parameters for searching Table 2 is called 'Description'.
Table 2 contains a list of 'resolutions', or solutions to a problem.

My problem is that it's easy to use LIKE, SOUNDEX or DIFFERENCE on any one term, but if the description is a long string, how can I query Table 2 for the closest matching solution?

For example, a typical case might be:

TABLE 1 DESCRIPTION:
 Today, my printer faield. In fact, it failed twice. I've checked the toner and power cable and both are OK. Can you help?

TABLE 2's RECORDS:
  Printers need regular topping up with Toner. If yours has run out, contact IT Support to get some.
  Has your printer stopped working? Check the printer cables are plugged in!
  If your mouse sticks, give it a clean. Sometimes dirt causes it to function improperly.


SO....................

Can we use each word individually in TABLE 1 DESCRIPTION to search through all the descriptions in TABLE 2, ranking by the most relevant first?

Cheers!
0
Comment
Question by:OnError_Fix
  • 4
  • 2
7 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13836759
You may look into Full Text Seach for this.  I have not worked with it but looks like a good candidate.

The hard part with this is having "each word individually search through all the descriptions".  First you have to have a function or stored procedure that will parse Table 1 Description and split it into individual words.  Then from this list, you have to get rid of common words such as "a", "the","it" and so forth.  I suggest creating a table that will list words that you don't want included in your search.

Once you have a list of words to search for in Table 2, then you have to process each word and count the number of times it appeared in the Description of Table 2.  This can easily be done by a user-defined function.
0
 

Author Comment

by:OnError_Fix
ID: 13836883
Hello,

Thanks for your help. Not sure full-text is the route I want to go down, to be honest. However, I'd be interested in a sample of the user-defined function you mentioned?

Thanks
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13836954
Here's a user-defined function that will return the number of times a search string appears in the given input:

CREATE FUNCTION [dbo].[ufn_CountString] ( @pInput VARCHAR(8000), @pSearchString VARCHAR(100) )
RETURNS INT
BEGIN

RETURN (LEN(@pInput) - LEN(REPLACE(@pInput, @pSearchString, ''))) / LEN(@pSearchString)

END
GO

Here's an example on how to use it:

DECLARE @vInput           VARCHAR(8000)
DECLARE @vSearchString    VARCHAR(50)

SET @vInput = 'THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG'
SET @vSearchString = 'THE'

SELECT dbo.ufn_CountString ( @vInput, @vSearchstring)

This returns 2 because the word "THE" occurs 2 times in the input.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:OnError_Fix
ID: 13840999
Fantastic function,

The problem I have now is how to count each word in the given Update ID, to return a set of results from Table 2 but with their rank caluclated in a single SUM column.

I.E.

@SearchKeywords = ('Quick', 'Fox')

Table 1                             Table 2
QUICK BROWN FOX...         1 + 1 = 2
JONNY FOX                        1 = 1

Any ideas how I can accomplish this?

I have written a vb.net function to filter out all the 'common' words, and generate a string of keywords for the remaining parts. Now all I gotta do is put SQL to work!

Thanks
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 1000 total points
ID: 13841510
check Red-Gate SQL Data Compare
 utility /Download a 14-day, fully functional, free trial./
http://www.red-gate.com/sql/SQL_Data_Compare_facts.htm
---------------------
Another way -> bcp in text files each column and compare files -
google search for compare text files:
http://www.google.com/search?as_q=&num=10&hl=en&btnG=Google+Search&as_epq=compare+text+files&as_oq=&as_eq=&lr=&as_ft=i&as_filetype=&as_qdr=all&as_occt=any&as_dt=i&as_sitesearch=&safe=images
0
 
LVL 28

Assisted Solution

by:rafrancisco
rafrancisco earned 1000 total points
ID: 13844043
I suggest looping through your search key words and loading the results into a temporary table then you can do a SUM on the temporary table.  It is something like this:

For each word in your word list
BEGIN
    INSERT INTO YourTempTable (Table2ID, SearchWord, WordCount)
    SELECT ID, @SearchWord, dbo.ufn_CountString ( Description, @SearchWord )
    FROM Table2

    Get Next Word
END

SELECT Table2ID, SUM(WordCount) AS WordCount
FROM YourTempTable
GROUP BY Table2ID
ORDER BY SUM(WordCount) DESC
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 14015885
OnError_Fix, were you able to solve this problem in any way?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how the fundamental information of how to create a table.
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.

840 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