How to use RowFilter to discard leading zeros

Question

In my table I have productcode defined as char (7) and filled it with leading zeros. I am using this as datasource and binding it to my DataGridView. I have search box on the form and it allows users search on product code. When user type any number or numbers in serach textbox I would like to filter datasource on product code starting with the number(s) user entered regarless of leading zeros. e.g if user enteres 3, datagridvew should show product id 0000033,0000035,0000301,0000343,0000344,0000349,0003123,0003467,  etc... when user type 34 in search box it should shows 0000343,0000344,0000349,0003467, etc...

Can anyone please help me how to use RowFilter?

Thanks.

 

dtSearchData.DefaultView.RowFilter

Answer 1

You can use below expression:

DataTable.DefaultView.RowFilter = "Convert(Convert(id,'System.Int32'),'System.String') Like '34%'";

---

Make everything as simple as possible, but not simpler.

Answer 2

This is not so easy task. But I did it again. its hard because you have to remove all leading zeros, and then check the numbers inserted into texBox and compater with all the number in the DataTable.

Ok, here is the example code:

 

public partial class Form1 : Form
  {
    DataTable table;
    public Form1()
    {
      InitializeComponent();
      table = new DataTable("myTable");
      table.Columns.Add(new DataColumn("Numbers", typeof(string)));
      //adding some example data:
      string[] array = { "00123", "00234", "00001", "01443", "12438", "00002", "34154", "03412", "00351" };
      DataRow dr;
      for (int i = 0; i < array.Length; i++)
      {
        dr = table.NewRow();
        dr["Numbers"] = array[i];
        table.Rows.Add(dr);
      }
      dataGridView1.DataSource = new BindingSource(table, null);
    }

    private void textBox1_TextChanged(object sender, EventArgs e)
    {
      string query = textBox1.Text.Trim();

      //create a copy of the main table:
      DataTable tableCopy = table.Copy();
      //create a additional boolean column (show or not to show the row):
      tableCopy.Columns.Add(new DataColumn("Show", typeof(bool)));
      for (int i = 0; i < table.Rows.Count; i++)
      {
        //removing zeros:
        int intValue = int.Parse(table.Rows[i]["Numbers"].ToString());
        tableCopy.Rows[i]["Numbers"] = intValue.ToString();
        string str = (tableCopy.Rows[i]["Numbers"].ToString().Length > query.Length) ? 
               tableCopy.Rows[i]["Numbers"].ToString().Substring(0, query.Length) : 
               tableCopy.Rows[i]["Numbers"].ToString();
        //define if the row must be shown or not:
        if (str == query)
          tableCopy.Rows[i]["Show"] = true;
        else
          tableCopy.Rows[i]["Show"] = false;
      }     
      //get filtered rows:
      DataRow[] rows = tableCopy.Select(String.Format("Numbers LIKE '{0}%'", query));

      //do selection on the main table (the one which is data bound):
      DataTable tableNEW = table.Clone();
      for (int i = 0; i < table.Rows.Count; i++)
      {
        //insert into new table if:
        if ((bool)tableCopy.Rows[i]["Show"] == true)
          tableNEW.Rows.Add(table.Rows[i]["Numbers"]);
      }

      //set new dataSource to DGV:
      dataGridView1.DataSource = new BindingSource(tableNEW, null);
    }
  }

It looks a bitcomplicated, there is maybe some impovements, but this code works :)

Hope you like it!

---

Mitja

Answer 3

Mitja,

I appreciate your time with the code. Great work. Only problem is I have about 5000 items in the list and I am affraid it will slow as user types.

Cem, Your code worked great and very quick. You are awsome.

I was trying following code but was getting error. Can you please look at it?

dtSearchResults.Defaultview.RowFilter = "[Product Id].ToString.TrimStart(" & Chr(34) & "0" & Chr(34) & "c) like '" & searchText & "%'"

Answer 4

I am sorry for late reply because time difference,

You cannot use VB/C# functions ([Product Id].ToString.TrimStart) inside the RowFilter expression.

Take a look at function part in the DataColumn.Expression article.

---

Make everything as simple as possible, but not simpler.

Answer 5

great Cem, Thank you very much.