1

I'm trying to get a full-text search running on SQL Server in Azure.

My data is in a varbinary(max) with all columns containing data. The data is strings of html.

The SearchableData column is computed and filled using:

CONVERT(VARBINARY(MAX),[Title] + [Body])

Doing a select and a convert back yields data.

I would like to utilize the built in html filter of SQL Server.

If I do the following I can search and everything works, however, without a filter: 

CREATE FULLTEXT INDEX 
    ON ArticleContent (Body LANGUAGE 0, Title LANGUAGE 0) 
       KEY INDEX PK_ArticleContent ON AcademyFTS 
       WITH (STOPLIST = SYSTEM, CHANGE_TRACKING AUTO)

However, I want to be able to utalize the .html filtering.

I've created the following: 

CREATE FULLTEXT CATALOG AcademyFTS WITH ACCENT_SENSITIVITY = OFF AS DEFAULT

and 

CREATE FULLTEXT INDEX 
    ON ArticleContent (SearchableData TYPE COLUMN FileExtension LANGUAGE 0) 
       KEY INDEX PK_ArticleContent ON AcademyFTS 
       WITH (STOPLIST = SYSTEM, CHANGE_TRACKING AUTO)

However the catalog is empty and I don't get any results back from a simple search

SELECT * 
FROM ArticleContent 
WHERE FREETEXT(SearchableData, 'wiki')

I've been using these two guides:

Hands on Full-Text Search in SQL Server

How to implement a full-text search on HTML documents with Microsoft SQL Server

Herlin
  • 353
  • 1
  • 5
  • 12
  • It should be added that the filters are installed and I've also had a look at Microsoft Docs. – Herlin Dec 18 '18 at 07:38
  • Have you tested storing HTML data as XML? Search results won’t include tagnames, attribute names, or words within comments – Alberto Morillo Dec 18 '18 at 12:55
  • I will have a try. I have gotten it to work with nvarchar fields but then I get no filtering. – Herlin Dec 18 '18 at 14:25
  • http://developmentnow.com/2006/08/12/sql-server-2005-full-text-search-on-html-documents/ – Alberto Morillo Dec 18 '18 at 14:27
  • They are using image and not varbinary(max), I do the same thing as they do. I still don't get results back. Something is wierd. Searching in varchar works just fine if I set the FT search index on body/title in this case. I don't get filtering however.. – Herlin Dec 19 '18 at 07:36

1 Answers1

2

I found the answer!

You can't full-text search on a computed column! :)

Microsoft Docs

Herlin
  • 353
  • 1
  • 5
  • 12