Solved

SQL Select - String match option

Posted on 2014-09-14
2
191 Views
Last Modified: 2014-09-30
Is it possible to have SQL Select that can compare words in string column from one file to another?  I need a SQL statement that can tell me the number of words that match between tow files.  

For example;  Let's say I have two files each contain one column containing a string of words.

File 1 - MatchString
Consumer Credit Report
Commerical Credit Report
FICO Score and Income Report
Commerical Assest and Income Report

File 2 - MatchString
Credit Report Category
Credit Score Category
Asset Category


Expected Results
Source File      Match-Field1      Match-Field2           Match Word Count
MatchFile1      Consumer Credit Report            
MatchFile2   Credit Report Category                             2
                  
MatchFile1      Commerical Credit Report            
MatchFile2     Credit Report Category                     1
MatchFile2     Credit Score Category                             1
                  
MatchFile1      FICO Score and Income Report            
MatchFile2      Credit Score Category                             1
                  
MatchFile1      Commerical Assest and Income Report            
MatchFile2      N/A                                                             0
0
Comment
Question by:hojohappy
[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 25

Accepted Solution

by:
chaau earned 500 total points
ID: 40322478
You can try to do so with a help of a FULL TEXT INDEX. I have created a small example here
Basically, you create a full text index on one of your tables. Then use the words from the second table as a keywords for the CONTAINS function. You will need to do so using a cursor.
    create table t1 (id int NOT NULL, t varchar(2000));
    create table t2 (id int NOT NULL, t varchar(2000));
    CREATE FULLTEXT CATALOG ft AS DEFAULT;
    CREATE UNIQUE INDEX ui_t1 ON t1(id);
    CREATE FULLTEXT INDEX ON t1 (t) KEY INDEX ui_t1 WITH STOPLIST = SYSTEM;
    
    INSERT INTO t1 values
    (1, 'Andrew was here'),
    (2, 'Pete sleeps here');
    
    INSERT INTO t2 values
    (1, 'Pete was here'),
    (2, 'Andrew sleeps here');
    
    declare @t varchar(2000)
    declare @id int
    declare c CURSOR FOR SELECT id, t FROM t2
    OPEN c 
    FETCH NEXT FROM c INTO @id, @t
    WHILE @@FETCH_STATUS = 0
    BEGIN
      set @t = REPLACE(@t, ' ', ' OR ')
      select *, @t, @id from t1 where contains(t, @t)
      FETCH NEXT FROM c INTO @id, @t
    END
    CLOSE c
    DEALLOCATE c

Open in new window

Unfortunately , the SQL Fiddle at the link above does not show the correct output. I have tried it on a SQL Server and it works. Try it on your server and advise how it goes
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40322518
Do you really mean files or should be tables?
Because if it is really files you have better options than SQL Server to do the job.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

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.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

623 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