Comparing 2 tables where the table queried contains wildcards

Let me know if there’s a better way to do this than using 2 datatables and comparing. I would like to query a table that CONTAINS wildcards.

I receive a CSV with the following values, which we’ll call table1:

Description
ALKEYVALUE12375 jad8fh nonsense099383
AKKEYVALUE13878 j kd ike nonsense 81839 kd

I would like to compare it to another CSV (table2) that contains the KEYVALUE portion of the description, and return the associated state. Layout of table2:

State   |  Description
AL      |  ALKEYVALUE*
AK      |  AKKEYVALUE*

The only way I can think of making this work is to nest 2 for each loops where it goes through each row of table1, and then compares it to each row in table2. However, this seems very inefficient. Is there a better way of doing this?

How about if you do something like below?

Compare first 10 characters from Table A Description and Table B description which is XXKEYVALUE. The output will have Table A description and Table B State into a DataTable.

dtJoin=(From a In dt1.AsEnumerable() Join b In dt2.AsEnumerable() On a.Field(Of String)(“Description”).ToString.SubString(0,10) Equals b.Field(Of String)(“Description”).ToString.SubString(0,10) Select dtJoin.LoadDataRow(New Object() {a.Field(Of String)(“Description”),b.Field(Of String)(“State”)}, False)).CopyToDataTable

1 Like

Hey, how do we get all the columns for both the tables in this case?
Something like this using {a,b} -

dtJoin=(From a In dt1.AsEnumerable() Join b In dt2.AsEnumerable() On a.Field(Of String)(“Description”).ToString.SubString(0,10 ) Equals b.Field(Of String)(“Description”).ToString.SubString(0,10) Select dtJoin .LoadDataRow(New Object() {a,b}, False)).CopyToDataTable