Using Like statement with DataView RowFIlter

Question

I have a DataTable with some data and I want to filter with LIKE query so I have written a code like below and everything working fine, I'm generating the query and passing it to RowFilter object of DataView.

if (results.Count() == 2)
{
    if (results[0].ToString() == "" && results[1].ToString() != "")
    {
        searchString = String.Format("[" + searchBy + "] LIKE '%{0}'", results[1].ToString());
    }
    else if (results[0].ToString() != "" && results[1].ToString() == "")
    {
       searchString = String.Format("[" + searchBy + "] LIKE '{0}%'", results[0].ToString());
    }
    else if (results[0].ToString() != "" && results[1].ToString() != "")
    {
       searchString = String.Format("[" + searchBy + "] LIKE '{0}%{1}'", results[0].ToString(), results[1].ToString());
    }
}

if (searchString != "")
{
     DataView dv = new DataView(AccountList.Tables[0]);
     dv.RowFilter = searchString;
     var tab = dv.ToTable();
     DataSet data_set = new DataSet();
     data_set.Tables.Add(tab);
     ProcessDataSetAndLoadData(data_set);
}

In above code segment is working fine except last else if condition, In that condition I'm getting the searchString like this,

[AccountCode] LIKE 'c%l'

When I passing this to RowFilter I'm getting the eror like below,

Error in Like operator: the string pattern 'c%l' is invalid.

From all other conditions I'm getting the searchString like below and those conditions are working fine.

[AccountCode] LIKE '%c%'
[AccountCode] LIKE 'c%'
[AccountCode] LIKE '%c

My Question is why I'm getting the error only for the string like this ?. [AccountCode] LIKE 'c%l'

Please Advise me to complete my task.

Kiran Beladiya Kiran Beladiya · Accepted Answer · 2017-06-06T05:52:56

In DataView filter, wildcard characters are not allowed in middle of the string.

Please refer this MSDN article: https://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx which says

Wildcard Characters

Both the * and % can be used interchangeably for wildcard characters in a LIKE comparison. If the string in a LIKE clause contains a * or %, those characters should be enclosed in brackets ([]). If a bracket is in the clause, each bracket character should be enclosed in brackets (for example [[] or []]). A wildcard is allowed at the start and end of a pattern, or at the end of a pattern, or at the start of a pattern. For example:

"ItemName LIKE 'product'"

"ItemName LIKE '*product'"

"ItemName LIKE 'product*'"

Wildcard characters are not allowed in the middle of a string. For example, 'te*xt' is not allowed.

Using Like statement with DataView RowFIlter

2 Answers