Text/string analysis in SQL Server

Question

I am trying to analyze large amount of text in SQL Server to calculate overall score based on number repetitive words from two different tables. I am looking for a query to do this.

To make it easier I am showing as example below.

TABLE 1:

id | Message                   | 
--  ---------------------------
 1 | mike magic                | 
 2 | sky blue and dark         |
 3 | wars star                 | 
 4 | whistle mountain broke    |  

TABLE 2 (plus):

id | Words        | score
--  -------------- ------
 1 | mike         | +1
 2 | dark         | +1
 3 | wars         | +1

TABLE 3 (minus):

id | Words        | score
--  -------------- ------
 1 | whistle      | -1
 2 | mountain     | -1
 3 | magic        | -1

Desired result:

id | Message                   | plus| minus| sum |
--  --------------------------- ----- ------ -----
 1 | mike magic                |  +1 | -1   |  0  |
 2 | sky blue and dark         |  +1 |  0   | +1  |
 3 | wars star                 |  +1 |  0   | +1  | 
 4 | whistle mountain broke    |   0 | -2   | -2  |

Answer 1

You can use the below query:

--create table table1 (id int,message varchar(100),[date] date ,[other info] varchar(100));
--insert into table1 values
--(1,'mike magic', '2016-01-01','some other information'),
--(2,'sky blue and dark', '2016-01-02','some other information'),
--(3,'wars star', '2016-10-01','some other information'),
--(4,'whistle mountain broke', '2016-02-01','some other information');
--create table table2 (id int,words varchar(100), score int);
--insert into table2 values
--(1,'mike','+1'),
--(2,'dark','+1'),
--(3,'wars','+1');

--create table table3 (id int,words varchar(100), score int);
--insert into table3 values
--(1,'whistle','-1'),
--(2,'mountain','-1'),
--(3,'magic','-1');

select 
    t1.id, t1.message, t1.date,t1.[other info], 
    isnull(sum(cast(t2.score as int)),0) plus, 
    isnull(sum(cast(t3.score as int)),0) minus,
    isnull(sum(cast(t2.score as int)),0) + isnull(sum(cast(t3.score as int)),0) [sum]

from 
    table1 t1 
        left join table2 t2 
                on ' '+ t1.message+' ' like '% '+t2.Words+' %'
        left join table3 t3 
                on ' '+ t1.message+' ' like '% '+t3.Words+' %'
group by t1.id, t1.message, t1.date,t1.[other info]