4

I'm trying to query FullTextSearch indexed column for hashtags

SELECT Bio FROM Users
WHERE CONTAINS (Bio,'promoter')

SELECT Bio FROM Users
WHERE CONTAINS (Bio,'#promoter')

both results returns the same record

promoter of #Art

I tried also to put double quotes around the searched term "#promoter" but same result.

The same problem when I'm searching for '@something'

What I need to do in order FullTextSearch to index the special characters?

5

Hmm I'm afraid '#' counts as punctuation and SQL Server Full-Text Indexing was invented before Twitter. There are however a couple of approaches:

1) Pre-processing

Use the full-text functions to fetch most of the data then refine it with Like, eg

SELECT Id
INTO #tmp
FROM dbo.Users
WHERE CONTAINS ( Bio, '#promoter' )

SELECT *
FROM dbo.Users u
WHERE u.Bio Like '%#promoter%'
AND EXISTS ( SELECT * FROM #tmp t WHERE u.Id = t.Id )

Whilst you could use CONTAINSTABLE to do the same thing in one query, you can't necessarily guarantee what order the plan will take - and why would you, T-SQL is after all declarative. I prefer to be explicit, but if you find performance acceptable and/or data volumes are low, combine the two, eg

SELECT *
FROM dbo.Users u
    INNER JOIN CONTAINSTABLE ( Users, Bio, '"#promoter"' ) ct ON u.Id = ct.[key]
WHERE u.Bio Like '%#promoter%'

I did some simple tests on a 1 million row table and it seemed to perform ok. YMMV.

2) Tokenised version of column

Store an alternate version, eg a computed column where '#' is replaced with the literal text 'hashtag', something like this:

-- Add computed column
ALTER TABLE dbo.Users ADD cleanBio AS REPLACE ( Bio, '#', 'hashtag' )

-- Add the computed column to full-text index
ALTER FULLTEXT INDEX ON dbo.Users ADD ( cleanBio ) 
GO

SELECT * FROM dbo.Users
WHERE CONTAINS ( cleanBio, 'hashtagpromoter' )

Obviously there is some storage overhead here.

Consider trying these approaches with your data and let me know how you get on.

  • Thanks @wBob I prefer your second solution. I found that '#' hashtag sign is a WordBreaker so I am trying to find out how to remove from Language word-breaker list. – Nikola Mitev Sep 3 '15 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.