How to do Content Search for a Phrase (More than one word)

Question

User-835633664 posted

Hi Team,

We have a requirement to do Content search. With below listed code the search is providing results separate words and not the complete phrase. So it works fine for single word search but for Phrase search it returns the results with single words also. Hence the result in not only for phase content.

The requirement is to perform the content search for a Phrase.

Private Function searchByContent() As DataTable
        Try
            Using con As New OleDbConnection("Provider = Search.CollatorDSO; Extended Properties = Application = Windows")
                If con.State <> System.Data.ConnectionState.Open Then
                    con.Open()
                End If

                Using cmd As New OleDbCommand()

                    Dim sharedPath As String = ConfigurationManager.AppSettings("sharedPath")
                    Dim serverName As String = ConfigurationManager.AppSettings("serverName")

                    cmd.CommandText = "Select SYSTEM.ITEMNAME FROM " + serverName + ".SystemIndex WHERE SCOPE='" + sharedPath + "' And freetext('" + txtSearchTerm.Text + "')"
                    cmd.Connection = con

                    Using da As New OleDbDataAdapter(cmd)
                        Using dtTable As New DataTable()
                            da.Fill(dtTable)

                            Return (dtTable)
                        End Using
                    End Using
                End Using
            End Using
        Catch ex As Exception

            System.Diagnostics.Debug.WriteLine(ex.Message)
            Return Nothing
        End Try
    End Function

Regards,

Bala N

Answer 1

User475983607 posted

Use LIKE or CONTAINS.

Reference documentation.

https://docs.microsoft.com/en-us/sql/relational-databases/search/full-text-search

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql

Answer 2

User347430248 posted

Hi bala01092017,

you can Use CONTAINS and CONTAINSTABLE to match words and phrases.

you can Use FREETEXT and FREETEXTTABLE to match the meaning, but not the exact wording.

example:

USE AdventureWorks2012  
GO  

SELECT Title  
FROM Production.Document  
WHERE FREETEXT (Document, 'vital safety components')  
GO  

USE AdventureWorks2012  
GO  

SELECT FT_TBL.ProductDescriptionID,  
   FT_TBL.Description,   
   KEY_TBL.RANK  
FROM Production.ProductDescription AS FT_TBL INNER JOIN  
   CONTAINSTABLE (Production.ProductDescription,  
      Description,   
      '(light NEAR aluminum) OR  
      (lightweight NEAR aluminum)'  
   ) AS KEY_TBL  
   ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]  
WHERE KEY_TBL.RANK > 2  
ORDER BY KEY_TBL.RANK DESC;  
GO  

USE AdventureWorks2012  
GO  

SELECT KEY_TBL.RANK, FT_TBL.Description  
FROM Production.ProductDescription AS FT_TBL   
     INNER JOIN  
     FREETEXTTABLE(Production.ProductDescription, Description,  
                    'perfect all-around bike') AS KEY_TBL  
     ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]  
ORDER BY KEY_TBL.RANK DESC  
GO  

for more example and detailed description , you can visit link below.

Query with Full-Text Search

Regards

Deepak

Answer 3

User-835633664 posted

Hi Deepak,

I tried with your approach here you've used AdventureWorks2012 Database whereas ourselves we're trying to do search in side files.  So this approach didn't help me now I'm getting an error after chagned the query where clause from FreeText to Contains.  I attached the code block for your reference.

Try
            Using con As New OleDbConnection("Provider = Search.CollatorDSO; Extended Properties = Application = Windows")
                If con.State <> System.Data.ConnectionState.Open Then
                    con.Open()
                End If

                Using cmd As New OleDbCommand()

                    Dim sharedPath As String = ConfigurationManager.AppSettings("sharedPath")
                    Dim serverName As String = ConfigurationManager.AppSettings("serverName")

                    cmd.CommandText = "Select SYSTEM.ITEMNAME FROM " + serverName + ".SystemIndex WHERE SCOPE='" + sharedPath + "' And contains('" + txtSearchTerm.Text + "')"

                    cmd.Connection = con

                    Using da As New OleDbDataAdapter(cmd)
                        Using dtTable As New DataTable()
                            da.Fill(dtTable)

                            Return (dtTable)
                        End Using
                    End Using
                End Using
            End Using
        Catch ex As Exception

            System.Diagnostics.Debug.WriteLine(ex.Message)
            Return Nothing
        End Try

Here Server name is z3clsweb01 and Shared path is file://z3clsweb01\e.

And the error I'm getting is

"One or more errors occurred during processing of command. IErrorInfo.GetDescription failed with E_FAIL(0x80004005)".

The above error is coming for both contains and containstable keyword.

Regards,

Bala N

Answer 4

User347430248 posted

Hi bala01092017,

try to print the cmd.CommandText and check it looks correct or not.

if it looks correct then try to directly run in SQL.

check whether it gives any error or execute correctly.

if it gives any error then try to correct it in SQL and try to use that corrected query in your code.

if you just see the cmd.CommandText then you will not able to find the mistake in it.

let us know about your testing result. we will try to provide further suggestion to solve the issue.

Regards

Deepak