Filter Datatable with dynamic values

Help Activities
, , ,
1

I need to match a value that may not always be named exactly as stored within the Datatable, typically names where the first and last name position may not always correctly placed. This could potentially be further complicated when we deal with Asian names where there are more syllabuses along with their Christan name. Would I still be able to retrieve the desired value via filter Datatable?

Some example would be as follows:
Name in Datatable - Jason Lim Kai Rong

Potential Names given to compare:
ā€˜Lim Kai Rongā€™, ā€˜Kai Rongā€™, ā€˜Kai Rong Limā€™, ā€˜Jason Limā€™, ā€˜Lim Kai Rong, Jasonā€™

The list can go on but is there any solution to cover these uncertainties?

Thank you in advance.

2

Hey I believe,If no exact match is found, you can use string manipulation functions like Contains, IndexOf, or regular expressions to check for partial matches.

Regards,

3

@QTH
If the possible names are limited in number you can try this

Yourstringarray is array of string datatype

YourstringArray={ā€œLim Kai Rongā€,ā€œKai Rongā€}

Blockquote

dt = dt.AsEnumerable.Where(function(r) yourStringArray.Contains(r(ā€œColumnNameā€).ToString())).CopyToDataTable

Cheers

4

Hope this expression helps you resolve this

Letā€™s take like u have a array or list of string variable with all possible values u want to compare
Being a list variable you can add values any time to that collection

Now use a assign a gyro compare the dt - datatable column with that list variable and return only then

matchedRows = (From row In dataTable.AsEnumerable()
               Where listOfValues.Contains(row("ColumnNameToCompare").ToString.Trim)
               Select row).CopyToDataTable()

Cheers @QTH

5

Due to the lengthy list of names in the database, itā€™s practically impossible to maintain a list of array for each name. Are there potentially other directions I could look into?

1 Like
6

Then have a template file and get the column names as a list out of it which u can use for comparing with the current table

Some or other way we need to have a actual reference to compare with the current one in order to find the difference

@QTH

7

@QTH

Can you please let us know how are you getting the different names now?

are you getting in a datatable as a row or as column?

if so we can convert them to list as needed and then use it for comparision

if column - dt.AsEnumerable.Select(function(x) x("ColumnName").ToString).ToArray()

If row - dt.Rows(0).itemArray.ToArray()

To Compare - ListArray.Any(function(x) NameToCompare.Contains(x)) - this gives true if the name contains any of the values in the list

cheers