Word analysis with SQL

• Sam Vanga

  SSCommitted

  Points: 1742

  More actions

  January 18, 2013 at 10:54 am

  #268831

  I did a blog post earlier called PASS Summit Twitter Dashboard[/url]. It shows all tweets by people at the PASS summit.

  I now want to expand that by measuring the sentiment score. Logic to measure the score is below:

  there are a list of pre defined good and bad words with a score of 1 and -1 respectively.

  for each tweet, remove punctuation marks from text.

  compare words from each tweet with the predefined words list.

  get score based on matched words.

  sentiment score is the sum of scores from the above match.

  The logic may be inaccurate, but from what i learnt this model is currently being researched and is acceptable.

  Here is the sample DDL.

  Predefined words and score:

  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) ;

  Tweets:

  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!!!')

  Result:

  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

  For example, score for 'New Bond movie is #awesome!' is 1 because after removing punctuation mark (!) word awesome matches with a word in the Words table and score is 1.

  Score for 'I hear dirty reviews. Product X is a fail. #fail' = 3 because of the words dirty, fail, and fail (after removing #).

  Query should be able to perform with a huge data set, approximately 100K rows.

  Many thanks for your time and input.

  Sam.

  Sam Vanga
  http://SamuelVanga.com

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  January 18, 2013 at 11:18 am

  #1578823

  you might want to take a look at the proc dm_fts_parser

  It might just provide the word analysis you are looking for...

  Link : http://msdn.microsoft.com/en-us/library/cc280463(v=sql.100).aspx

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]