DataTable.Select string function in where clause

Question

I'm having problems with a DataTable.Select() where the matching values might contain leading spaces and need to be trimmed correctly to return the correct amount of records.

Currently my code is returning less records as the matching fails because of unwanted characters.

How do you handle DataTable.Select as the example SQL below suggests?

SELECT * FROM Table WHERE LTRIM(FullName) = ' Joe Smith'

I' tried

dataTable.Select("LTRIM(FullName) = ' Joe Smith'");

but it failed.

Any ideas?

Answer 1

I would suggest to use Linq-To-DataSet instead, it makes it a lot clearer and easier to maintain:

var rows = from row in dataTable.AsEnumerable()
           where row.Field<string>("FullName").Trim() == "Joe Smith"
           select row;

If you want to use LTRIM instead, you just have to replace Trim with TrimStart.

if you want an array or list, use ToArray or ToList, e.g.

DataRow[] rowsArray = rows.ToArray();

or a DataTable

dataTable = rows.CopyToDataTable();

Edit: if you insist on using DataTable.Select or you can't use linq, this should work(LTRIM is not supported):

rowsArray = dataTable.Select("TRIM(FullName) = 'Joe Smith'");

Answer 2

Give this a try:

string searchTerm = " Joe Smith";
string expression = String.Format("TRIM(FullName) = '{0}'", searchTerm.Trim());
dataTable.Select(expression);