Apr 17, 2014

How to create custom or user defined StopList and add and remove stop words or noise words in sql server

Creating a user define empty stop list in sql server:

CREATE FULLTEXT STOPLIST [CustomStopList]

Creating a user define stop list and copy the stop words from system stop list in sql server:

CREATE FULLTEXT STOPLIST [CustomStopList]
FROM SYSTEM STOPLIST

Adding a stopword in a stop list:

ALTER FULLTEXT STOPLIST [CustomStopList]
ADD ','
LANGUAGE 'English'

Deleting a stopword in a stop list:

ALTER FULLTEXT STOPLIST [CustomStopList]
DROP ','

LANGUAGE 'English'

Script to add all system stop words:

ALTER FULLTEXT STOPLIST [CustomStopList] ADD '$' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD '0' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD '1' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD '2' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD '3' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD '4' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD '5' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD '6' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD '7' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD '8' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD '9' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'A' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'B' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'C' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'D' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'E' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'F' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'G' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'H' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'I' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'J' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'K' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'L' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'M' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'N' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'O' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'P' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'Q' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'R' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'S' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'T' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'U' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'V' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'W' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'X' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'Y' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'Z' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'about' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'after' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'all' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'also' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'an' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'and' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'another' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'any' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'are' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'as' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'at' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'be' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'because' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'been' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'before' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'being' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'between' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'both' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'but' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'by' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'came' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'can' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'come' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'could' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'did' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'do' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'does' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'each' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'else' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'for' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'from' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'get' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'got' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'had' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'has' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'have' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'he' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'her' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'here' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'him' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'himself' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'his' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'how' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'if' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'in' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'into' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'is' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'it' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'its' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'just' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'like' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'make' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'many' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'me' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'might' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'more' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'most' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'much' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'must' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'my' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'never' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'no' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'now' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'of' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'on' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'only' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'or' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'other' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'our' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'out' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'over' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 're' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'said' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'same' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'see' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'should' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'since' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'so' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'some' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'still' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'such' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'take' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'than' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'that' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'the' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'their' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'them' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'then' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'there' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'these' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'they' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'this' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'those' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'through' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'to' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'too' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'under' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'up' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'use' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'very' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'want' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'was' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'way' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'we' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'well' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'were' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'what' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'when' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'where' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'which' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'while' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'who' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'will' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'with' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'would' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'you' LANGUAGE 'English'
ALTER FULLTEXT STOPLIST [CustomStopList] ADD 'your' LANGUAGE 'English'   

No comments:

Post a Comment