CODEWITHSUNDEEP
c#selectdatatablesubstring
DRapp
DRapp

Reputation: 48129

Substring test within DataTable.Select()

I have a DataTable with results already pulled down from a back end. I want to do a DataTable.Select(), but the criteria is based on a SUBSTRING of one of the columns.

Is there syntax to use in the Select() method that allows for substring of a column test, or do I have to do it the hard way -- scan each row.

Upvotes: 5

Views: 19611

Answers (4)

schlebe
schlebe

Reputation: 3716

In 2024, I use SUBSTRING and LEN functions in same complex filter's expression.

This code is used in VB.net source

        sFilter =
<sql>
  (  alias_nom = <%= AddQuote(sNom) %>
  OR (search_mode = 'STARTS' 
        AND alias_nom = SUBSTRING(<%= AddQuote(sNom) %>,1,LEN(alias_nom)))
  )
  AND (ref_structured IS NULL 
        OR ref_structured = <%= AddQuote(sRefStructured) %>)
</sql>

        Dim oRows = dt.Select(sFilter, sOrder)
        For Each oRow As DataRow In oRows
            sNewNom = oRow("new_nom")
            sNewRefStructured = oRow("ref_structured") & ""

I use <sql>text</sql> format to create a string on multiple lines so that filter is more readable.

This filter's expression is working correctly and is used in a production program.

Upvotes: 0

bill_texas-rulz
bill_texas-rulz

Reputation: 59

You can use substring. 

DataRow[] selectRowsWithSubstring;
selectRowsWithSubstring = datatable.Select("substring(column, start, length) = value");

Upvotes: 0

Webleeuw
Webleeuw

Reputation: 7272

Maybe you could use linq, like the following example:

var x = from c in table.AsEnumerable()
        select c.Field<string>("MyColumn").Substring(index, length);

or

var x = from c in table.AsEnumerable()
        select c.Field<string>("MyColumn").Contains("MySearchString");

Upvotes: 4

Amnon
Amnon

Reputation: 7772

You can use the LIKE operator in the expression given to Select():

table.Select("ItemName LIKE '*product*'")

Upvotes: 7

Related Questions