CREATE TABLE #Words
(
Id int identity(1,1) primary key
, Word char(10)
, Score int) ;

INSERT #Words
(Word, Score)
VALUES ('Good',1)
		, ('Awesome', 1)
		, ('Super', 1)
		, ('Bad', -1)
		, ('Fail', -1)
		, ('Dirty', -1) ;

CREATE TABLE #Text
(Id int identity(1,1) primary key
, [Text] varchar(140))

INSERT #Text
([Text])
VALUES 
('New Bond movie is #awesome!')
, ('I hear dirty reviews. Product X is a fail. #fail')
, ('I am neutral!!!')

CREATE TABLE #Result
([Text] varchar(140), Score int)

INSERT #Result
VALUES
('New Bond movie is #awesome!',1)
, ('I hear dirty reviews. Product X is a fail. #fail',3)
, ('I am neutral!!!',0)

SELECT  *
FROM    #Result

SELECT a.ID, a.[Text], TotalScore=ISNULL(SUM(c.Score), 0)
FROM #Text a
CROSS APPLY PatternSplitCM([Text], '[A-Za-z]') b
LEFT JOIN #Words c ON b.Item = c.Word
GROUP BY a.ID, a.[Text]