Preppin Data Challenge - Week 9, Sql Server

Preppin Data Challenge - Week 9

-------SplitWords----function-------- http://madprops.org/blog/T-SQL-to-Split-a-varchar-into-Words/ -------------------------
CREATE FUNCTION SplitWords(@text varchar(8000))
   RETURNS @words TABLE (
      pos smallint primary key,
      value varchar(8000)
   )
AS
BEGIN
   DECLARE
      @pos smallint,
      @i smallint,
      @j smallint,
      @s varchar(8000)

SET @pos = 1
   WHILE @pos <= LEN(@text) 
   BEGIN 
      SET @i = CHARINDEX(' ', @text, @pos)
      SET @j = CHARINDEX(',', @text, @pos)
      IF @i > 0 OR @j > 0
      BEGIN
         IF @i = 0 OR (@j > 0 AND @j < @i)
            SET @i = @j

IF @i > @pos
         BEGIN
            -- @i now holds the earliest delimiter in the string
            SET @s = SUBSTRING(@text, @pos, @i - @pos)

INSERT INTO @words
            VALUES (@pos, @s)
         END 
         SET @pos = @i + 1

WHILE @pos < LEN(@text) 
            AND SUBSTRING(@text, @pos, 1) IN (' ', ',')
            SET @pos = @pos + 1 
      END 
      ELSE 
      BEGIN 
         INSERT INTO @words 
         VALUES (@pos, SUBSTRING(@text, @pos, LEN(@text) - @pos + 1))

SET @pos = LEN(@text) + 1 
      END 
   END 
   RETURN 
END
-------SplitWords----function-------- http://madprops.org/blog/T-SQL-to-Split-a-varchar-into-Words/ -------------------------

----- create input table for 'Complaints' - Week 9 Challenge
CREATE TABLE W9_Complaints(Tweet VARCHAR(4000))

-- insert data into 'Complaints' table
INSERT INTO W9_Complaints 
SELECT Tweet FROM 
(
    SELECT 'Hey @C&BSudsCo you suds are soap...I expected beer!' AS Tweet UNION ALL
    SELECT 'WTF?! You’re Soap has just filled my bathroom full of bubbles!! Way to bubbly for me @C&BSudsCo' AS Tweet UNION ALL
    SELECT 'What kind of moron name is @C&BSudsCo?' AS Tweet UNION ALL
    SELECT 'No where near enough bubbles from your Soap Bar @C&BSudsCo. I wanted a bar of soap, not a chocolate bar' AS Tweet UNION ALL
    SELECT 'My wife has accused me of having an affair you morons. You’ve over perfumed your Soap Bar @C&BSudsCo' AS Tweet UNION ALL
    SELECT 'I just wanted a bar of soap, not to smell like a brothel?! Do you even smell your own products @C&BSudsCo' AS Tweet UNION ALL
    SELECT 'Your soap has made my beard itchy, what the hell do you put in it?' AS Tweet UNION ALL
    SELECT '@C&BSudsCo when r u coming to Paris?' AS Tweet UNION ALL
    SELECT 'I just bought my first @C&BSudsCo soap! Because I`m worth it! Actually I`m worth a lot more!' AS Tweet UNION ALL
    SELECT '@C&BSudsCo Who thought glitter in a beard shampoo was a good idea???' AS Tweet UNION ALL
    SELECT '@C&BSudsCo OMG my beard fell off!!' AS Tweet UNION ALL
    SELECT '@C&BSudsCo I hate liquid soap, I only like Bars' AS Tweet
) As t1

-- see the 'Complaints' table data
-- SELECT * FROM W9_Complaints

----- create input table for 'Common English Words' - Week 9 Challenge
CREATE TABLE W9_CommonEnglishWords(Rank INT, Word VARCHAR(20))

-- insert data into 'Common English Words' table
INSERT INTO W9_CommonEnglishWords 
 SELECT Rank, Word FROM 
 (
  SELECT '1' AS Rank, 'the' AS Word UNION ALL
SELECT '2' AS Rank, 'of' AS Word UNION ALL
SELECT '3' AS Rank, 'to' AS Word UNION ALL
SELECT '4' AS Rank, 'and' AS Word UNION ALL
SELECT '5' AS Rank, 'a' AS Word UNION ALL
SELECT '6' AS Rank, 'in' AS Word UNION ALL
SELECT '7' AS Rank, 'is' AS Word UNION ALL
SELECT '8' AS Rank, 'it' AS Word UNION ALL
SELECT '9' AS Rank, 'you' AS Word UNION ALL
SELECT '10' AS Rank, 'that' AS Word UNION ALL
SELECT '11' AS Rank, 'he' AS Word UNION ALL
SELECT '12' AS Rank, 'was' AS Word UNION ALL
SELECT '13' AS Rank, 'for' AS Word UNION ALL
SELECT '14' AS Rank, 'on' AS Word UNION ALL
SELECT '15' AS Rank, 'are' AS Word UNION ALL
SELECT '16' AS Rank, 'with' AS Word UNION ALL
SELECT '17' AS Rank, 'as' AS Word UNION ALL
SELECT '18' AS Rank, 'I' AS Word UNION ALL
SELECT '19' AS Rank, 'his' AS Word UNION ALL
SELECT '20' AS Rank, 'they' AS Word UNION ALL
SELECT '21' AS Rank, 'be' AS Word UNION ALL
SELECT '22' AS Rank, 'at' AS Word UNION ALL
SELECT '23' AS Rank, 'one' AS Word UNION ALL
SELECT '24' AS Rank, 'have' AS Word UNION ALL
SELECT '25' AS Rank, 'this' AS Word UNION ALL
SELECT '26' AS Rank, 'from' AS Word UNION ALL
SELECT '27' AS Rank, 'or' AS Word UNION ALL
SELECT '28' AS Rank, 'had' AS Word UNION ALL
SELECT '29' AS Rank, 'by' AS Word UNION ALL
SELECT '30' AS Rank, 'hot' AS Word UNION ALL
SELECT '31' AS Rank, 'but' AS Word UNION ALL
SELECT '32' AS Rank, 'some' AS Word UNION ALL
SELECT '33' AS Rank, 'what' AS Word UNION ALL
SELECT '34' AS Rank, 'there' AS Word UNION ALL
SELECT '35' AS Rank, 'we' AS Word UNION ALL
SELECT '36' AS Rank, 'can' AS Word UNION ALL
SELECT '37' AS Rank, 'out' AS Word UNION ALL
SELECT '38' AS Rank, 'other' AS Word UNION ALL
SELECT '39' AS Rank, 'were' AS Word UNION ALL
SELECT '40' AS Rank, 'all' AS Word UNION ALL
SELECT '41' AS Rank, 'your' AS Word UNION ALL
SELECT '42' AS Rank, 'when' AS Word UNION ALL
SELECT '43' AS Rank, 'up' AS Word UNION ALL
SELECT '44' AS Rank, 'use' AS Word UNION ALL
SELECT '45' AS Rank, 'word' AS Word UNION ALL
SELECT '46' AS Rank, 'how' AS Word UNION ALL
SELECT '47' AS Rank, 'said' AS Word UNION ALL
SELECT '48' AS Rank, 'an' AS Word UNION ALL
SELECT '49' AS Rank, 'each' AS Word UNION ALL
SELECT '50' AS Rank, 'she' AS Word UNION ALL
SELECT '51' AS Rank, 'which' AS Word UNION ALL
SELECT '52' AS Rank, 'do' AS Word UNION ALL
SELECT '53' AS Rank, 'their' AS Word UNION ALL
SELECT '54' AS Rank, 'time' AS Word UNION ALL
SELECT '55' AS Rank, 'if' AS Word UNION ALL
SELECT '56' AS Rank, 'will' AS Word UNION ALL
SELECT '57' AS Rank, 'way' AS Word UNION ALL
SELECT '58' AS Rank, 'about' AS Word UNION ALL
SELECT '59' AS Rank, 'many' AS Word UNION ALL
SELECT '60' AS Rank, 'then' AS Word UNION ALL
SELECT '61' AS Rank, 'them' AS Word UNION ALL
SELECT '62' AS Rank, 'would' AS Word UNION ALL
SELECT '63' AS Rank, 'write' AS Word UNION ALL
SELECT '64' AS Rank, 'like' AS Word UNION ALL
SELECT '65' AS Rank, 'so' AS Word UNION ALL
SELECT '66' AS Rank, 'these' AS Word UNION ALL
SELECT '67' AS Rank, 'her' AS Word UNION ALL
SELECT '68' AS Rank, 'long' AS Word UNION ALL
SELECT '69' AS Rank, 'make' AS Word UNION ALL
SELECT '70' AS Rank, 'thing' AS Word UNION ALL
SELECT '71' AS Rank, 'see' AS Word UNION ALL
SELECT '72' AS Rank, 'him' AS Word UNION ALL
SELECT '73' AS Rank, 'two' AS Word UNION ALL
SELECT '74' AS Rank, 'has' AS Word UNION ALL
SELECT '75' AS Rank, 'look' AS Word UNION ALL
SELECT '76' AS Rank, 'more' AS Word UNION ALL
SELECT '77' AS Rank, 'day' AS Word UNION ALL
SELECT '78' AS Rank, 'could' AS Word UNION ALL
SELECT '79' AS Rank, 'go' AS Word UNION ALL
SELECT '80' AS Rank, 'come' AS Word UNION ALL
SELECT '81' AS Rank, 'did' AS Word UNION ALL
SELECT '82' AS Rank, 'my' AS Word UNION ALL
SELECT '83' AS Rank, 'sound' AS Word UNION ALL
SELECT '84' AS Rank, 'no' AS Word UNION ALL
SELECT '85' AS Rank, 'most' AS Word UNION ALL
SELECT '86' AS Rank, 'number' AS Word UNION ALL
SELECT '87' AS Rank, 'who' AS Word UNION ALL
SELECT '88' AS Rank, 'over' AS Word UNION ALL
SELECT '89' AS Rank, 'know' AS Word UNION ALL
SELECT '90' AS Rank, 'water' AS Word UNION ALL
SELECT '91' AS Rank, 'than' AS Word UNION ALL
SELECT '92' AS Rank, 'call' AS Word UNION ALL
SELECT '93' AS Rank, 'first' AS Word UNION ALL
SELECT '94' AS Rank, 'people' AS Word UNION ALL
SELECT '95' AS Rank, 'may' AS Word UNION ALL
SELECT '96' AS Rank, 'down' AS Word UNION ALL
SELECT '97' AS Rank, 'side' AS Word UNION ALL
SELECT '98' AS Rank, 'been' AS Word UNION ALL
SELECT '99' AS Rank, 'now' AS Word UNION ALL
SELECT '100' AS Rank, 'find' AS Word UNION ALL
SELECT '101' AS Rank, 'any' AS Word UNION ALL
SELECT '102' AS Rank, 'new' AS Word UNION ALL
SELECT '103' AS Rank, 'work' AS Word UNION ALL
SELECT '104' AS Rank, 'part' AS Word UNION ALL
SELECT '105' AS Rank, 'take' AS Word UNION ALL
SELECT '106' AS Rank, 'get' AS Word UNION ALL
SELECT '107' AS Rank, 'place' AS Word UNION ALL
SELECT '108' AS Rank, 'made' AS Word UNION ALL
SELECT '109' AS Rank, 'live' AS Word UNION ALL
SELECT '110' AS Rank, 'where' AS Word UNION ALL
SELECT '111' AS Rank, 'after' AS Word UNION ALL
SELECT '112' AS Rank, 'back' AS Word UNION ALL
SELECT '113' AS Rank, 'little' AS Word UNION ALL
SELECT '114' AS Rank, 'only' AS Word UNION ALL
SELECT '115' AS Rank, 'round' AS Word UNION ALL
SELECT '116' AS Rank, 'man' AS Word UNION ALL
SELECT '117' AS Rank, 'year' AS Word UNION ALL
SELECT '118' AS Rank, 'came' AS Word UNION ALL
SELECT '119' AS Rank, 'show' AS Word UNION ALL
SELECT '120' AS Rank, 'every' AS Word UNION ALL
SELECT '121' AS Rank, 'good' AS Word UNION ALL
SELECT '122' AS Rank, 'me' AS Word UNION ALL
SELECT '123' AS Rank, 'give' AS Word UNION ALL
SELECT '124' AS Rank, 'our' AS Word UNION ALL
SELECT '125' AS Rank, 'under' AS Word UNION ALL
SELECT '126' AS Rank, 'name' AS Word UNION ALL
SELECT '127' AS Rank, 'very' AS Word UNION ALL
SELECT '128' AS Rank, 'through' AS Word UNION ALL
SELECT '129' AS Rank, 'just' AS Word UNION ALL
SELECT '130' AS Rank, 'form' AS Word UNION ALL
SELECT '131' AS Rank, 'much' AS Word UNION ALL
SELECT '132' AS Rank, 'great' AS Word UNION ALL
SELECT '133' AS Rank, 'think' AS Word UNION ALL
SELECT '134' AS Rank, 'say' AS Word UNION ALL
SELECT '135' AS Rank, 'help' AS Word UNION ALL
SELECT '136' AS Rank, 'low' AS Word UNION ALL
SELECT '137' AS Rank, 'line' AS Word UNION ALL
SELECT '138' AS Rank, 'before' AS Word UNION ALL
SELECT '139' AS Rank, 'turn' AS Word UNION ALL
SELECT '140' AS Rank, 'cause' AS Word UNION ALL
SELECT '141' AS Rank, 'same' AS Word UNION ALL
SELECT '142' AS Rank, 'mean' AS Word UNION ALL
SELECT '143' AS Rank, 'differ' AS Word UNION ALL
SELECT '144' AS Rank, 'move' AS Word UNION ALL
SELECT '145' AS Rank, 'right' AS Word UNION ALL
SELECT '146' AS Rank, 'boy' AS Word UNION ALL
SELECT '147' AS Rank, 'old' AS Word UNION ALL
SELECT '148' AS Rank, 'too' AS Word UNION ALL
SELECT '149' AS Rank, 'does' AS Word UNION ALL
SELECT '150' AS Rank, 'tell' AS Word UNION ALL
SELECT '151' AS Rank, 'sentence' AS Word UNION ALL
SELECT '152' AS Rank, 'set' AS Word UNION ALL
SELECT '153' AS Rank, 'three' AS Word UNION ALL
SELECT '154' AS Rank, 'want' AS Word UNION ALL
SELECT '155' AS Rank, 'air' AS Word UNION ALL
SELECT '156' AS Rank, 'well' AS Word UNION ALL
SELECT '157' AS Rank, 'also' AS Word UNION ALL
SELECT '158' AS Rank, 'play' AS Word UNION ALL
SELECT '159' AS Rank, 'small' AS Word UNION ALL
SELECT '160' AS Rank, 'end' AS Word UNION ALL
SELECT '161' AS Rank, 'put' AS Word UNION ALL
SELECT '162' AS Rank, 'home' AS Word UNION ALL
SELECT '163' AS Rank, 'read' AS Word UNION ALL
SELECT '164' AS Rank, 'hand' AS Word UNION ALL
SELECT '165' AS Rank, 'port' AS Word UNION ALL
SELECT '166' AS Rank, 'large' AS Word UNION ALL
SELECT '167' AS Rank, 'spell' AS Word UNION ALL
SELECT '168' AS Rank, 'add' AS Word UNION ALL
SELECT '169' AS Rank, 'even' AS Word UNION ALL
SELECT '170' AS Rank, 'land' AS Word UNION ALL
SELECT '171' AS Rank, 'here' AS Word UNION ALL
SELECT '172' AS Rank, 'must' AS Word UNION ALL
SELECT '173' AS Rank, 'big' AS Word UNION ALL
SELECT '174' AS Rank, 'high' AS Word UNION ALL
SELECT '175' AS Rank, 'such' AS Word UNION ALL
SELECT '176' AS Rank, 'follow' AS Word UNION ALL
SELECT '177' AS Rank, 'act' AS Word UNION ALL
SELECT '178' AS Rank, 'why' AS Word UNION ALL
SELECT '179' AS Rank, 'ask' AS Word UNION ALL
SELECT '180' AS Rank, 'men' AS Word UNION ALL
SELECT '181' AS Rank, 'change' AS Word UNION ALL
SELECT '182' AS Rank, 'went' AS Word UNION ALL
SELECT '183' AS Rank, 'light' AS Word UNION ALL
SELECT '184' AS Rank, 'kind' AS Word UNION ALL
SELECT '185' AS Rank, 'off' AS Word UNION ALL
SELECT '186' AS Rank, 'need' AS Word UNION ALL
SELECT '187' AS Rank, 'house' AS Word UNION ALL
SELECT '188' AS Rank, 'picture' AS Word UNION ALL
SELECT '189' AS Rank, 'try' AS Word UNION ALL
SELECT '190' AS Rank, 'us' AS Word UNION ALL
SELECT '191' AS Rank, 'again' AS Word UNION ALL
SELECT '192' AS Rank, 'animal' AS Word UNION ALL
SELECT '193' AS Rank, 'point' AS Word UNION ALL
SELECT '194' AS Rank, 'mother' AS Word UNION ALL
SELECT '195' AS Rank, 'world' AS Word UNION ALL
SELECT '196' AS Rank, 'near' AS Word UNION ALL
SELECT '197' AS Rank, 'build' AS Word UNION ALL
SELECT '198' AS Rank, 'self' AS Word UNION ALL
SELECT '199' AS Rank, 'earth' AS Word UNION ALL
SELECT '200' AS Rank, 'father' AS Word UNION ALL
SELECT '201' AS Rank, 'head' AS Word UNION ALL
SELECT '202' AS Rank, 'stand' AS Word UNION ALL
SELECT '203' AS Rank, 'own' AS Word UNION ALL
SELECT '204' AS Rank, 'page' AS Word UNION ALL
SELECT '205' AS Rank, 'should' AS Word UNION ALL
SELECT '206' AS Rank, 'country' AS Word UNION ALL
SELECT '207' AS Rank, 'found' AS Word UNION ALL
SELECT '208' AS Rank, 'answer' AS Word UNION ALL
SELECT '209' AS Rank, 'school' AS Word UNION ALL
SELECT '210' AS Rank, 'grow' AS Word UNION ALL
SELECT '211' AS Rank, 'study' AS Word UNION ALL
SELECT '212' AS Rank, 'still' AS Word UNION ALL
SELECT '213' AS Rank, 'learn' AS Word UNION ALL
SELECT '214' AS Rank, 'plant' AS Word UNION ALL
SELECT '215' AS Rank, 'cover' AS Word UNION ALL
SELECT '216' AS Rank, 'food' AS Word UNION ALL
SELECT '217' AS Rank, 'sun' AS Word UNION ALL
SELECT '218' AS Rank, 'four' AS Word UNION ALL
SELECT '219' AS Rank, 'thought' AS Word UNION ALL
SELECT '220' AS Rank, 'let' AS Word UNION ALL
SELECT '221' AS Rank, 'keep' AS Word UNION ALL
SELECT '222' AS Rank, 'eye' AS Word UNION ALL
SELECT '223' AS Rank, 'never' AS Word UNION ALL
SELECT '224' AS Rank, 'last' AS Word UNION ALL
SELECT '225' AS Rank, 'door' AS Word UNION ALL
SELECT '226' AS Rank, 'between' AS Word UNION ALL
SELECT '227' AS Rank, 'city' AS Word UNION ALL
SELECT '228' AS Rank, 'tree' AS Word UNION ALL
SELECT '229' AS Rank, 'cross' AS Word UNION ALL
SELECT '230' AS Rank, 'since' AS Word UNION ALL
SELECT '231' AS Rank, 'hard' AS Word UNION ALL
SELECT '232' AS Rank, 'start' AS Word UNION ALL
SELECT '233' AS Rank, 'might' AS Word UNION ALL
SELECT '234' AS Rank, 'story' AS Word UNION ALL
SELECT '235' AS Rank, 'saw' AS Word UNION ALL
SELECT '236' AS Rank, 'far' AS Word UNION ALL
SELECT '237' AS Rank, 'sea' AS Word UNION ALL
SELECT '238' AS Rank, 'draw' AS Word UNION ALL
SELECT '239' AS Rank, 'left' AS Word UNION ALL
SELECT '240' AS Rank, 'late' AS Word UNION ALL
SELECT '241' AS Rank, 'run' AS Word UNION ALL
SELECT '242' AS Rank, 'don`t' AS Word UNION ALL
SELECT '243' AS Rank, 'while' AS Word UNION ALL
SELECT '244' AS Rank, 'press' AS Word UNION ALL
SELECT '245' AS Rank, 'close' AS Word UNION ALL
SELECT '246' AS Rank, 'night' AS Word UNION ALL
SELECT '247' AS Rank, 'real' AS Word UNION ALL
SELECT '248' AS Rank, 'life' AS Word UNION ALL
SELECT '249' AS Rank, 'few' AS Word UNION ALL
SELECT '250' AS Rank, 'stop' AS Word
 ) As t1
 
 -- see the data 
 -- SELECT Rank, Word FROM W9_CommonEnglishWords

---- create a temp table to hold formatted tweets data
    IF(OBJECT_ID('tempdb..#tbl_Complaints') IS NOT NULL) Begin  Drop Table #tbl_Complaints End
    CREATE TABLE #tbl_Complaints(tweetID INT IDENTITY(1,1), tweet VARCHAR(MAX), tweet_Original VARCHAR(MAX))
  INSERT INTO #tbl_Complaints SELECT REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(Tweet,'.',''), '?', ''), ',', ''),'!',''), '@C&BSudsCo', ''), Tweet FROM W9_Complaints
-- see the data from temp table
-- SELECT * FROM #tbl_Complaints

---- create a temp table to hold the words from tweets data
IF(OBJECT_ID('tempdb..#tbl_Words') IS NOT NULL) Begin  Drop Table #tbl_Words End
CREATE TABLE #tbl_Words(Sno INT IDENTITY(1,1), tweetID INT, word VARCHAR(50))

-- using a WHILE loop, split each tweet into words and inserts into '#tbl_Words' table
DECLARE @i INT
SET @i = 1
WHILE(@i<= (SELECT COUNT(*) FROM #tbl_Complaints) )
BEGIN
	INSERT INTO #tbl_Words SELECT @i, value FROM  SplitWords((SELECT tweet FROM #tbl_Complaints WHERE tweetID = @i))
	SET @i = @i + 1
END

---- run the fina query to see the output
SELECT word, tweet FROM 
 (
    SELECT l1.word, l1.tweetID FROM #tbl_Words l1 LEFT OUTER JOIN W9_CommonEnglishWords r1 ON l1.word = r1.Word WHERE r1.Rank IS NULL
 ) As l LEFT OUTER JOIN #tbl_Complaints r ON l.tweetID = r.tweetID ORDER BY word


run \| edit \| history \| help	0