I’m having trouble with full text search in SQL Server when I try to look for terms that include the hash character. Here’s what I’m attempting to do:
SELECT * FROM Products WHERE FREETEXT(Description, 'f#')
I have a full text index on the Description column, but this query returns no results, even though there are records that have ‘f#’. In contrast, when I use a standard LIKE operator, it works perfectly:
SELECT * FROM Products WHERE Description LIKE '%f#%'
This situation leads me to think that the # character might have a special significance in full text search. Is there a way I can escape it or change my FREETEXT query to accommodate the hash symbol properly? Any assistance would be greatly appreciated.
Interesting problem! Have you tried using CONTAINS with exact phrase syntax? What SQL Server version are you running? Fulltext behavior sometimes changes between versions. Does this happen with other special characters like @ or &?
The hash symbol breaks words in SQL Server full-text search - that’s why your FREETEXT query isn’t working. FREETEXT does linguistic analysis and treats special characters like # as separators, not part of the actual search term. I had the same issue when searching technical docs, and switching to CONTAINS with double quotes fixed it right away. Your CONTAINS(Description, '"f#"') approach works because it forces the engine to treat the whole string (including the hash) as one literal phrase. If you’re constantly searching terms with special characters, you might want to look at customizing your word breaker configuration.
totally get ur issue! the # is a word boundary in fulltext searches, messes with your terms. try this out: CONTAINS(Description, '"f#"') instead. it should find what ur lookin for!