1

I want to filter a text in all columns in a datagridview. So I use Like % in the filter text. However, I found that the text(strFilter in my code) may contain % sometimes. So it have a syntax error. How can I use filter with like clause when the filter text contains %? 

    Private Sub PassFilter(ByRef dataTable As DataTable, ByVal strFilter As String)
    Try
        Dim dataview As DataView = dataTable.DefaultView
        Dim strExp As String = ""
        For Each col As DataColumn In dataTable.Columns
            If Not String.IsNullOrEmpty(strFilter) Then
                If strExp = "" Then
                    strExp += "[" & col.ColumnName & "]" & " LIKE '%" & strFilter & "%'"
                Else
                    strExp += " OR " & "[" & col.ColumnName & "]" & " LIKE '%" & strFilter & "%'"
                End If
            End If
        Next

        dataview.RowFilter = strExp

    Catch ex As Exception
        ShowMessage("Unable to filter datagridview." + ex.Message)
    End Try


End Sub
Summer
  • 241
  • 2
  • 10
  • 23
  • Same question, different character. http://stackoverflow.com/questions/439495/how-can-i-escape-square-brackets-in-a-like-clause – Tony Hopkinson Feb 13 '12 at 15:54

2 Answers2

1

try to escape % into square brackets: % ->[%], with a replace before appliyng the filter.

Angelo Badellino
  • 2,141
  • 2
  • 24
  • 45
  • I tried to replace: If strFilter.Contains("%") Then strFilter.Replace("%", "[" & "%" & "]") End If But it is still % without brackets..... – Summer Feb 13 '12 at 16:12
  • Try with this statement: If strFilter.Contains("%") Then strFilter.Replace("%", "[%]") End If – Angelo Badellino Feb 13 '12 at 16:23
  • Try **strFilter =** strFilter.Replace(...). Replace returns a different string, it does not alter strFilter. Strings are immutable in .Net – MarkJ Feb 13 '12 at 18:57
0

Here's the "correct" way - use an "escape sequence":

http://msdn.microsoft.com/en-us/library/ms378045.aspx

ResultSet rst =  
  stmt.executeQuery("SELECT col3 FROM test1 WHERE col2  LIKE '\\%moose' {escape '\\'}");

Another (non-portable) way in SQL Server is to use square brackets:

ResultSet rst =  
  stmt.executeQuery("SELECT col3 FROM test1 WHERE col2  LIKE '[%]moose'");
paulsm4
  • 114,292
  • 17
  • 138
  • 190