Select datatable + Substring

Help Studio
, ,
1

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,

2

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,

3

Hi,

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

4

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?

5

Thank you for your quick answer to my question, sorry

6

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) ;
7

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.

8

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.

9

How would you do it?

Thank you very much for your help.

10

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)

11

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)

12

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

Thank you for your kindness.

13

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

1 Like
14

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

Thanks.