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

  • 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

    Friday, September 1, 2017 1:58 PM

All replies

  • 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

    Monday, September 4, 2017 5:57 AM
  • 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

    Wednesday, September 6, 2017 10:13 AM
  • 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

    Wednesday, September 13, 2017 9:44 AM