Indicate how many times are matched from keywords for each matched record

Question

How to get many times are matched from keywords for each matched record from csv files.

That I need to search all the characters in the [StringData] in the csv file to find out the records which contain the words and many times that are matched in the StringData on the Text box Search.

Code

        string search = txtBoxSearch.Text;
        string pathOnly = Path.GetDirectoryName(csvPath);
        string fileName = Path.GetFileName(csvPath);

        string sql = @"SELECT F1 AS StringID, F2 AS StringContent FROM [" + fileName + "] WHERE F2 LIKE '%" + search + "%'";

        using (OleDbConnection connection = new OleDbConnection(
                  @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly +
                  ";Extended Properties=\"Text;HDR=No\""))
        using (OleDbCommand command = new OleDbCommand(sql, connection))
        using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
        {
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            GridViewResult.DataSource = dataTable;
            GridViewResult.DataBind();
        }

score 1 · Accepted Answer · answered Nov 16 '20 at 07:54

You can add a new column to DataTable and calculate it's value on memory foreach rows by using Regex.Matches:

string search = txtBoxSearch.Text;
string pathOnly = Path.GetDirectoryName(csvPath);
string fileName = Path.GetFileName(csvPath);

string sql = @"SELECT F1 AS StringID, F2 AS StringContent FROM [" + fileName + "] WHERE F2 LIKE '%" + search + "%'";

using (OleDbConnection connection = new OleDbConnection(
          @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly +
          ";Extended Properties=\"Text;HDR=No\""))
using (OleDbCommand command = new OleDbCommand(sql, connection))
using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
    DataTable dataTable = new DataTable();
    adapter.Fill(dataTable);
    dataTable.Columns.Add("MatchTimes", typeof(System.Int32));
    
    foreach(DataRow row in dataTable.Rows)
    {
        row["MatchTimes"] = Regex.Matches(row["StringContent"].ToString(), row["StringID"].ToString()).Count
    }

    GridViewResult.DataSource = dataTable;
    GridViewResult.DataBind();
}

See also: Count the number of times a string appears within a string

But I want to ask is there a way to put REGEXP_COUNT in the Oledb query ? So I don't need to loop again the data — Player 2nd, Nov 16 '20 at 08:29

Indicate how many times are matched from keywords for each matched record

1 Answers1