Using wildcards in between a C# DataTable Select statement

Question

I'm trying to look for a pattern in a C# DataTable. I essentially have my code like:

long count= table.Select("Col0 LIKE '%AA%ZZ%'").Count();

However, I get an exception System.Data.EvaluateException: Error in Like operator: the string pattern '%AA%ZZ%' is invalid.

I read that wildcards can only be used at the beginning or the end in a C# select statement. Is there an alternative?

Ideally, I don't want to write a loop that goes through every row of the table myself.

Andrew · Answer

If you are only interested in how the string begins (that is, the % at the end), you can use this approach. Suppose you want this condition: "LIKE 'Code02%'". For that, you could use:
long count = table.Select("Col0 >= 'Code02' AND Col0 < 'Code03'").Count();

This filter will return rows with values like Code02, Code020, Code02AC, Code02ZZZZZZZ, etc.

Alexander Petrov · Answer

You can use Linq to DataSet and regular expressions.

int count = table.AsEnumerable()
    .Where(row =>
    {
        string value = row.Field<string>("Col0");
        return Regex.IsMatch(value, ".*AA.*ZZ.*");
    })
    .Count();

Mukesh Adhvaryu · Answer

Ok Since you explained that there could be other stuff between AA and ZZ you are trying '%AA%ZZ%' (which throws error), try this:

long count= table.Select("Col0 LIKE 'AA%' AND Col0 LIKE '%ZZ'").Count();

This will select each row where Col0 begins with "AA" and Ends with "ZZ" which means anything in between does not play any role.

Using wildcards in between a C# DataTable Select statement

3 Answers

Add your own answers!

Ask a Question