Select datatable + Substring

Hi! I have a problem. I need to select rows in a datatable. The condition to be selected is that 7 first characters of a specific column (string) are different from “GENERAL”. I would like to do it with a select command within an Assign box, but it does not work.

tabla_lab.Select("[Column1].tostring.trim.substring(0,7).tostring.trim <>‘GENERAL’ ")

Can you help me to fix it?

Thank you very much,

And how would I do it if instead of the condition being placed on the 7 first characters of the field, it would be place on wherever this combination, “GENERAL” might be within the field?

Thank you very much for your help,

Hi,

You can use Like operator in the query. Please check below link and see if it helps.

Thank you very much SShubham for your quick question.

The problem is that I need to extract the rows that match the condition to a datarow to delete them from the original datatable, and this solution seems to need to store the result into an array, isn’t it?

Thank you for your quick answer to my question, sorry

If I understand your problem correctly, you want to delete the matching rows from the datatable. right?

To delete rows from datatable, you could use the logic like this:

  1. Use the select function on the datatable to filter the rows that match your condition and this will return an array of Datarows.
  2. Then use a for each loop and remove each row from the Array of Datarows using datatable.Rows.Remove(row) ;

Thanks again. But, how do I do step 1 when I need to select rows extracting only a part of a string field in a column? When I tried to use your initial suggestion, I got an error, saying that I could not use a IEnumerable method to get a variable of type datarow.

My data table is “tablab”, the column I am interested in is “Name”, and the rows I need to delete are those which do not include the collection “GENERAL” within the field in column “Name”.

Note: Column “Name” cells are fields of variable lenght, so “GENERAL” may be a part of it.

How would you do it?

Thank you very much for your help.

Attached is the code for your reference. I have used filter activity to remove the rows of matching condition.
The output is saved in the same datatable.
Please check.

Test.zip (8.3 KB)

Hi @ccrespo9669 ,

You an try below linq to get rows which so not contains “General”

(From r In dtInput Where Not r.Item(“Department”).ToString.ToUpper.Contains(“GENERAL”) Select dtOutput.LoadDataRow(New Object() { r.Field(of Object)(“Name”),r.Field(of Object)(“Department”) },False)).CopyToDataTable

You can change column accordingly.
test.xaml (5.8 KB)
GetRowFromSubstring.xlsx (9.0 KB)

Thnak you very much to both of you. I will check and come back to you in a while.

Thank you for your kindness.

Solved. Great! Thank you very much for your help both of you. I am saving a lot of computational time. Thanks!!!

1 Like

Good to know that, please mark the valid solution from above comments.

Thanks.