locked
DataTable select with substring RRS feed

  • Question

  • Hi

    I have a datatable with data like:

    col1          col2                              col3              col4

    test        test data                        50                true

    test        test result  - master       100              true

    test        test result - number1      75                true

    test        test result - number2     100               true

    test        test test                         100              true

    test        not test                          100              false

    How can I select rows which col 4 is true and distinct col2 where if it contains '-' only take the text before the '-' e.g. I would be left with:

    col1          col2                              

    test        test data                       

    test        test result    

    test        test test                         

    Thanks

    Paul

    Sunday, August 2, 2020 9:29 PM

All replies

  • Hi Paul,

    Thank you for posting here.

    We can modify the data using loops first and then use linq to  find the appropriate data.

                foreach (DataRow item in dataTable.Rows)
                {
                    if (item["col2"].ToString().StartsWith("test result"))
                    {
                        item["col2"] = "test result";
                    }
                }
    
                var re1 = dataTable.AsEnumerable().Where(row => Convert.ToBoolean(row["col4"]) == true).Distinct(new DataRowComparer());
                var re = re1.CopyToDataTable();

            public class DataRowComparer : IEqualityComparer<DataRow>
            {
                public bool Equals(DataRow row1, DataRow row2)
                {
                    return (row1.Field<string>("col2") == row2.Field<string>("col2"));
                }
                public int GetHashCode(DataRow t)
                {
                    return t.ToString().GetHashCode();
                }
            }

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 3, 2020 2:37 AM
  • Hi

    Try the bellow code

     

      var b = "true";

    var re1 = (from d in dataTable.AsEnumerable() where d.Field<string>("col4") == b //If type is boolean then pass boolean select new { c1 = d.Field<string>("col2").ToString().Contains("-") ? d.Field<string>("col2").ToString().Substring(0, d.Field<string>("col2").ToString().IndexOf("-")) : d.Field<string>("col2").ToString() }).Distinct().ToList();


    hanks and regards


    • Edited by Laxmidhar sahoo Tuesday, August 4, 2020 4:41 PM
    • Proposed as answer by Naomi N Tuesday, August 4, 2020 4:53 PM
    Tuesday, August 4, 2020 4:40 PM
  • Hi,

    Has your issue been resolved?

    If so, please click on the "Mark as answer" option of the reply that solved your question, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 7, 2020 8:10 AM