{"id":3452,"date":"2021-09-16T07:00:00","date_gmt":"2021-09-16T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=3452"},"modified":"2023-05-16T22:03:36","modified_gmt":"2023-05-16T19:03:36","slug":"pos-mporoyme-na-kanoyme-anazitisi-kei","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-na-kanoyme-anazitisi-kei\/","title":{"rendered":"How to Text Search Documents Stored in Database Table in SQL Server Using Full-Text Index"},"content":{"rendered":"

In SQL Server we have the possibility to store files such as docx, xlsx, XML, etc. in table fields. To search for words in these fields, a full table scan would have to be performed, but there is also the solution of creating one Full-Text Index<\/strong>. With the Full-Text Index we can search for words and phrases in table fields that are text, varchar, varbinary, char, image, etc. much more efficiently.<\/p>\n\n\n\n

To activate the Full-Text Index service<\/h2>\n\n\n\n

In SQL Server the Full-Text Index service is installed as a separate feature and runs as a separate service.<\/p>\n\n\n\n

To see if it is already installed and activated we run the following queries, if the value is 0 it means that it has not been installed \/ activated:<\/p>\n\n\n\n

SELECT SERVERPROPERTY('IsFullTextInstalled');  \nGO  \n\nSELECT is_fulltext_enabled\nFROM sys.databases\nWHERE database_id = DB_ID();<\/pre>\n\n\n\n
In case it is not installed, we should proceed to install the feature.<\/p>\n\n\n\n
So we open it ISO <\/em><\/strong>of the SQL Server installation, we choose Installation <\/strong><\/em>and New SQL Server stand-alone installation or add features to an existing installation<\/em><\/strong>.<\/p>\n\n\n\n
\"\"
1<\/figcaption><\/figure>\n\n\n\n
In the Installation Type <\/em><\/strong>we choose Add features to an existing instance of SQL Server<\/strong><\/em>:<\/p>\n\n\n\n
\"\"
2<\/figcaption><\/figure>\n\n\n\n
And finally to Feature Selection<\/em><\/strong>, we select it Full-Text and Semantic Extraction for Search<\/em><\/strong>:<\/p>\n\n\n\n
\"\"
3<\/figcaption><\/figure>\n\n\n\n
Then we go to SQL Server Configuration Manager<\/em><\/strong>, SQL Server Services<\/em><\/strong> and we select right click and Start<\/em><\/strong>.<\/p>\n\n\n\n
\"\"
4<\/figcaption><\/figure>\n\n\n\n
But there is one issue, by default it does not support all document types for Office<\/strong> like docx, xlsx, pptx and we should install it Microsoft Office 2010 Filter Pack<\/strong>.<\/p>\n\n\n\n
We download the Microsoft Office 2010 Filter Pack from here<\/a> and we install it.<\/p>\n\n\n\n
After the installation is complete, run the following query that will pass the changes:<\/p>\n\n\n\n
EXEC sp_fulltext_service 'update_languages'; \nEXEC sp_fulltext_service 'load_os_resources', 1;\nEXEC sp_fulltext_service 'restart_all_fdhosts';<\/pre>\n\n\n\n
Then by running the following query we can see that it has also added these document types to the supported ones:<\/p>\n\n\n\n
SELECT * FROM sys.fulltext_document_types;<\/pre>\n\n\n\n
\"\"
5<\/figcaption><\/figure>\n\n\n\n
How do we load the files into a table field<\/h2>\n\n\n\n
We had seen you another article<\/a> detailing how we can load files into the varbinary field of the table and how to export them back to a folder.<\/p>\n\n\n\n
To begin with, we will have to make the table in which we will insert them. In this table we will define an identity field with sequential numbering, the file name, the file type and finally a type field varbinary(max)<\/strong> which will contain the entire file in binary <\/strong>form.<\/p>\n\n\n\n
--drop table docs;\ncreate table mig_db.dbo.docs (\nid int identity(1,1),\ndocname nvarchar(200),\nfiletype varchar(100),\ndocument varbinary(max)\n);\n\nalter table mig_db.dbo.docs add constraint pk_docs primary key clustered(id);\n<\/pre>\n\n\n\n
All we have to do now is load in the array with insert <\/strong>one by one the files we want. The field that will contain the file is loaded as blob <\/strong>(binary large object) directly with the known function openrowset<\/a>, as shown in the following examples:<\/p>\n\n\n\n
insert into mig_db.dbo.docs\nselect N'doc1.docx','.docx',bulkcolumn  from openrowset (bulk N'c:\\SQL2019\\doc1.docx', single_blob) as lob;\n\ninsert into mig_db.dbo.docs\nselect N'doc2.docx','.docx',bulkcolumn  from openrowset (bulk N'c:\\SQL2019\\doc2.docx', single_blob) as lob;\n\ninsert into mig_db.dbo.docs\nselect N'doc3.doc','.doc',bulkcolumn  from openrowset (bulk N'c:\\SQL2019\\doc3.doc', single_blob) as lob;\n\ninsert into mig_db.dbo.docs\nselect N'excel1.xlsx','.xlsx',bulkcolumn  from openrowset (bulk N'c:\\SQL2019\\excel1.xlsx', single_blob) as lob;\n\ninsert into mig_db.dbo.docs\nselect N'\u03ad\u03bd\u03c4\u03c5\u03c0\u03bf.doc','.doc',bulkcolumn from openrowset (bulk N'c:\\SQL2019\\\u03ad\u03bd\u03c4\u03c5\u03c0\u03bf.doc', single_blob) as lob;<\/pre>\n\n\n\n
After we finish loading, if we select the table we will see these files in binary format:<\/p>\n\n\n\n
select * from mig_db..docs;<\/pre>\n\n\n\n
\"\"
6<\/figcaption><\/figure>\n\n\n\n
How we build the Full-Text Index<\/h2>\n\n\n\n
First we have to make one fulltext catalog<\/strong> which is where the full-text indexes will be stored. In this we set it to ignore tones.<\/p>\n\n\n\n
create fulltext catalog document_catalogue with accent_sensitivity = off; <\/pre>\n\n\n\n
In the next step we create the fulltext index<\/strong>. We define this docname with language <\/strong><\/em>'Greek' since the file can have a Greek name. We also define the field varbinary <\/strong>containing the file with the parameter column type<\/strong> in the field containing the document type.<\/p>\n\n\n\n
In the index as a key we define its name primary key<\/strong> that we made when creating the table and its name fulltext catalog<\/strong> that we want to be stored.<\/p>\n\n\n\n
By choice change_tracking auto<\/em><\/strong> we set the fulltext index to be updated automatically whenever changes are made:<\/p>\n\n\n\n
create fulltext index on dbo.docs\n(\n\tdocname language 'Greek',\n\tdocument type column filetype) \nkey index pk_docs on document_catalogue\nwith change_tracking auto, stoplist=system;<\/pre>\n\n\n\n
With the parameter stoplist<\/em><\/strong> we can touch words like 'and', 'the', 'that' etc. which do not help in the search. In addition to the system which is the default option, we can make a custom one where we add words we don't need:<\/p>\n\n\n\n
create fulltext stoplist trialstoplist from system stoplist;\nalter fulltext stoplist trialstoplist ADD '\u03b1\u03bb\u03bb\u03ac' Language 'Greek';\nalter fulltext index on docs set stoplist trialstoplist;<\/pre>\n\n\n\n
How we search the Full-Text Index<\/h2>\n\n\n\n
With the function contains <\/strong>by setting the varbinary field containing the file and the word we want, we can search the files in which it exists:<\/p>\n\n\n\n
select * from docs where contains(document,'dataplatform');<\/pre>\n\n\n\n
\"\"
7<\/figcaption><\/figure>\n\n\n\n
We can search in the same way in the filename field, which works even without the accent:<\/p>\n\n\n\n
select * from docs where contains(docname,'\u03b5\u03bd\u03c4\u03c5\u03c0\u03bf');<\/pre>\n\n\n\n
\"\"
8<\/figcaption><\/figure>\n\n\n\n
If we want the file to contain 2 different words, just put 'AND<\/em><\/strong>between them:<\/p>\n\n\n\n
select * from docs where contains(document,'SQL AND \u0394\u03bf\u03ba\u03b9\u03bc\u03b1\u03c3\u03c4\u03b9\u03ba\u03cc');<\/pre>\n\n\n\n
\"\"
9<\/figcaption><\/figure>\n\n\n\n
But if we want to search for a whole sentence it should be inside quotes <\/em>“” <\/strong>:<\/p>\n\n\n\n
select * from docs where contains(document,'\"SQL Server\"');<\/pre>\n\n\n\n
In the following file we see a text with a name and a surname which may not be connected as they are scattered within the file:<\/p>\n\n\n\n
\"\"
10<\/figcaption><\/figure>\n\n\n\n
If we try in the example again with 'AND<\/em><\/strong>' will bring us a result as there is no limit to the distance between the words:<\/p>\n\n\n\n
select * from docs where contains(document,'Giorgos and Gewrgiou');<\/pre>\n\n\n\n
\"\"
11<\/figcaption><\/figure>\n\n\n\n
If we would like to limit this and these words to have a distance between them of less than 3 words, it is done with the function near<\/strong>:<\/p>\n\n\n\n
select * from docs where contains(document,'near((Giorgos, Gewrgiou),3)');<\/pre>\n\n\n\n
As we can see this time it returned no results:<\/p>\n\n\n\n
\"\"
12<\/figcaption><\/figure>\n\n\n\n
In the next example we have a text that has the phrase SQL Server:<\/p>\n\n\n\n
\"\"
13<\/figcaption><\/figure>\n\n\n\n
If we use the function again contains <\/strong>but with the similar word 'servers<\/em>', will not return results:<\/p>\n\n\n\n
select * from docs where contains(document,'servers');<\/pre>\n\n\n\n
\"\"
14<\/figcaption><\/figure>\n\n\n\n
But we can use the function freetext <\/strong>which with the same word will also search for similar ones and will bring this document:<\/p>\n\n\n\n
select * from docs where freetext(document,'servers');<\/pre>\n\n\n\n
\"\"
15<\/figcaption><\/figure>\n\n\n\n
By using the function freetexttable <\/strong>and containstable<\/strong> we can also have a ranking in the results according to the frequency with which the word is mentioned and its weight:<\/p>\n\n\n\n
select * from freetexttable(dbo.docs, document,'dataplatform.gr') as ftt \njoin dbo.docs d on ftt.[key]=d.id order by ftt.rank desc;<\/pre>\n\n\n\n
\"\"
16<\/figcaption><\/figure>\n\n\n\n
Sources:<\/h2>\n\n\n\n