Sql-server – way stop the “.” (period) acting as a word breaker in FullText Indexes

full-text-searchsql servert-sql

I know the period is a word breaker however I want to be able to search the fulltext catalog for a field containing the phrase "ASP.Net" or similar.
Currently my search returns 'ASP' and 'Net' without them occurring consecutively in the text. I want to know if there is a way to stop the period (.) acting as a word breaker for specific searches?

My research so far just suggests I use a LIKE statement however this is not feasible as there are millions of rows with large text fields to search as quickly as possible.

Best Answer

Having followed the link from the comment by Doug Deden:

It looks like a multi-step process, but there are some promising ideas here: Customize the Behavior of Word Breakers with a Custom Dictionary and Custom WordBreaker for SQL Server Full-text on Stack Overflow.

I've seen that I will need to create a custom dictionary and add the entry 'ASP.Net'. This will ensure normal word breaks aren't applied to this phrase when crawling and/or searching on the full-text index.

Create a custom dictionary (SharePoint Server 2010)

Related Solutions

Postgresql – What does “GIN doesn’t support full index scans” mean

a word or two belonging to a pre-set, almost 'constant', list of words.

Have you considered scanning the texts as they are input and flagging them (possibly with a many-to-many link to another table containing your list of keywords)?

Sql-server – SQL Server Fulltext search against big amount of search terms over some period of time

Execution plan XML would be useful here but as a shot in the dark:

Daft as it may sound, try pushing either the [id] or date predicate into the CONTAINSTABLE query. See SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned - Consider embedding filter conditions as keywords in the indexed text.
I don't remember where but I recall reading an article or blog post some time ago that flagged big OR full-text queries as problematic. IIRC the suggested hack/workaround was to issue a UNION query instead.

For your example, the UNION for 2) would be along the lines of:

INSERT
    @processed_rules
    (
    rule_id
    , story_id
    )
SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term one"')

UNION

SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term two"')

UNION

  SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term three"')

Best Answer

Related Solutions

Postgresql – What does “GIN doesn’t support full index scans” mean

Sql-server – SQL Server Fulltext search against big amount of search terms over some period of time

Related Question