Solved

SQL Select - String match option

Posted on 2014-09-14
2
182 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 24

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 48

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

860 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