Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Select - String match option

Posted on 2014-09-14
2
Medium Priority
?
197 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 2000 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 52

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

670 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