Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Select - String match option

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

Independent Software Vendors: 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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

772 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