Db select statement with multiple 'likes'

Help
1

Hi,

I’m trying to filter a DataTable to certain values in a given column using either ‘like’ or ‘in’ sql syntax.

I’m trying to replicate the sql syntax of:

WHERE SUBSTRING([AColumn],1,100) in (‘01’,‘02’,‘03’)")

I’ve tried something like this but it seemed to return all results instead of just the matching ones.

This is what I have tried:

db.Select(“SUBSTRING([AColumn],1,100) in (‘01’,‘02’,‘03’)”).CopyToDataTable

The column in question could have values: 01,02,03,04,05.

I want to use config to assign the filter values…at present these filters could be one or many so I cannot use a ‘like’ statement combined with ‘or’ as there is no fixed index.

Any suggestions would be greatly appreciated.

2

Hi welcome to uipath community
Hope this would help you

**Yourdatatablename = Yourdatatablename.Select(“SubString(yourcolumnname,0,100) like ‘%01%’ or
SubString(yourcolumnname,0,100) like ‘%02%’ or
SubString(yourcolumnname,0,100) like ‘%03%’ “).CooyToDatatable()

Kindly try this and let know for any queries or clarification

Cheers @scottn

3

Hi @scottn

Since you want to use the Config to store the filter, you’ll need to create a string containg your statements
Write the filters in the config on the same line and separate them using “/” or you cand change it
Try something like

Dim filterArray as string() = Config(“filters”).tostring.split({“/”},stringsplitoptions.none)
dim bigFilter as string = “[ColumnA] = '” + String.Join(“’ or [ColumnA] = '”, filterArray) + “'”

Now that you have your filter, just apply it to the table

dt = db.select(bigFilter).copyToDataTable()

Just remember to change columnA to your column name from the excel file

Above you’ll see I have “'” which is a ’ included in double quotes “”
That’s because the select statement in vb.net looks something like this Select([Column] = ‘value’)

Hope this will help you

4

@Palaniyappan thanks for your response. But as stated the amount of filters on the column is elastic as derived from config. This will not work .

5

Thanks @paul.placintar - I did consider storing the entire query in config but was hoping there was a way where I could just pass a collection of columns.

I’ve kept it simple and just used multiple likes on a collection of columns using String.Split and then calling just the index for each column.

It will cause issues if the collection increases or decreases but it will do for the moment.

6

@scottn @Palaniyappan
Can be done with LINQ
I prototyped on stringlist based (see hint for DataTable below)

grafik
grafik1597×603 43.5 KB

(From s In sList1
From f In arrFilter
Where s.Contains(f)
Select s).toList

Take Note: J was not in FilterList so its not in the Filtered list.

For your Information I suggest:

  • in excel konfig separate the filter values by comma and create later an array from this by using split
  • adopt my statement to your needs, but keep the linebreaks

(From r In YourDTVar.AsEnumerable
From f In arrFilter
Where SubString(r(yourcolumnname)toString,0,100) like ‘%f%
Select r).CopyToDataTable

Let us know if itwas working, Thanks

7

Thanks. Looks like this might give me the flexibility I need. Will give it a go