0

I want to find only those row form table which contains numeric values , so for that I did as following 

dtDetails.Select(" (ISNUMERIC(OriginatingTransit)=0)")

but it throws exception 

The expression contains undefined function call ISNUMERIC().
Charles
  • 50,943
  • 13
  • 104
  • 142
Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
  • hmmm may be like this dtDetails.Select(" ISNUMERIC(OriginatingTransit)=0 ") – NoWar Jan 12 '12 at 13:42
  • Is the exception a `System.Data.EvaluateException`? If so see http://social.msdn.microsoft.com/Forums/is/adodotnetdataset/thread/d61ba2f5-c999-4fc1-aee0-e61415f439b8 – Rich O'Kelly Jan 12 '12 at 13:47
  • 1
    `ISNUMERIC` is almost always the wrong function to use - it answers a question that I've never found anyone wanting to ask ("Can this text be converted to at least one of the numeric data types?"), rather than the usual question most people have ("Can this text be converted to X?" where X is a *specific* data type). As an example, `1d2` is numeric by the `ISNUMERIC` definition, as is `£`. – Damien_The_Unbeliever Jan 12 '12 at 13:50

1 Answers1

1

You'll have to do that at the database level, or loop through yourself and write something that checks if a value can be parsed into a number. The Select method does not support what you're trying to do: Expression property on MSDN, contains info about what's supported.

bool IsNumeric(object o)
{
  decimal result_ignored;
  return o != null && 
    !(o is DBNull) && 
    decimal.TryParse(Convert.ToString(o), out result_ignored);
}

And then you can do something like this:

var filtered = dtDetails.Rows.Cast<DataRow>().
  Where(r => IsNumeric(r["OriginatingTransit"]));

If you then enumerate filtered, you will get only those with numeric values in that column.

This isn't perfect - because as some comments mention on the answer linked to by Surjit Samra in the comments to your question above, the exact meaning of 'is numeric' is loose. This goes for the widest possible description, I think, by using decimal to attempt to parse the value. You could use a different type (e.g. int or float), or a regular expression, perhaps, if your requirements are tighter.

Community
  • 1
  • 1
Andras Zoltan
  • 41,961
  • 13
  • 104
  • 160