Solved

SQL Select - String match option

Posted on 2014-09-14
2
189 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 50

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

734 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