How to know if a field is numeric in Linq To SQL

Question

I need to select the rows of a table where a column value is numeric, any Help?

EDIT: I have a varchar column and I need to select the ones that are numbers and the ones that are not.

EDIT 2: Integer.TryParse cannot be use because it cannot be translate to SQL.

Presumably, you would know because it's your table. Could you clarify a bit as to why this is not the case? — James Curran, Feb 17 '09 at 18:12
I have a varchar field and I need to select the ones that are numbers and the ones that not. — Jedi Master Spooky, Feb 17 '09 at 18:14
It can be done, very easily and with great performance using LINQ-to-SQL and built-in SQL functions... see my answer below. — Timothy Khouri, Jun 04 '09 at 12:23

score 15 · Answer 1 · answered Jun 04 '09 at 12:17

Open up your DBML (LINQ-to-SQL) file in an XML editor, go down to the end of the file and paste this just before the '</Database>' node:

<Function Name="ISNUMERIC" IsComposable="true">
    <Parameter Name="Expression" Parameter="Expression" Type="System.String" DbType="NVarChar(4000)" />
    <Return Type="System.Boolean" DbType="BIT NOT NULL"/>
</Function>

Now, you can use the already-in-SQL function called "ISNUMERIC". Here's how:

var blah = myDataContext.Accounts.Where(account=>
    myDataContext.ISNUMERIC(account.ID) == true);

There you go :)

You may also find these functions useful to copy:

<Function Name="RAND" IsComposable="true">
  <Return Type="System.Double" DbType="Float NOT NULL" />
</Function>
<Function Name="NEWID" IsComposable="true">
  <Return Type="System.Guid" DbType="UniqueIdentifier NOT NULL" />
</Function>

@billy I agree, except that's Entity Framework, and out well after the question was asked :) — Timothy Khouri, Jul 10 '12 at 17:38

score 7 · Accepted Answer · answered Feb 17 '09 at 18:29

I don't know if there is a mapping for int.TryParse() in LinqToSQL, but you could probably do it in two steps by performing the query, casting to a List, then selecting out of the list with LinqToObjects.

int i;
var query = context.Table.ToList();
var intQuery = query.Where( t => int.TryParse( t.Column, out i ) );

You might want to look at Dynamic LINQ, too. That would allow you to do something like:

var query = context.Table.Where( "IsNumeric(Column)" );

EDIT Dynamic LINQ is available in the VS2008 Code Samples, linked to from Scott Guthrie's blog, which I've linked above.

Regarding the first option: please be aware that are selecting the whole table and are only executing the where-criteria once the table is in-memory. This will not work well for larger data sets. — Maarten, Nov 22 '14 at 11:34

score 6 · Answer 3 · edited May 13 '15 at 14:31

SqlFunctions.IsNumeric

http://msdn.microsoft.com/en-us/library/system.data.objects.sqlclient.sqlfunctions.isnumeric.aspx

For example:

    private int GetLastGoodNumber(string PartNum)
    {
        return (from row in Db.SerialNo
                where
                   row.PartNum == PartNum
                let nullable = System.Data.Objects.SqlClient.SqlFunctions.IsNumeric(row.SerialNumber)
                where nullable != null
                select nullable.Value).Max();
    }

score 1 · Answer 4 · edited Jan 13 '12 at 11:59

1

<>.Where(x=>"0123456789".IndexOf(x.value.substring(0,1))>-1)

edited Jan 13 '12 at 11:59

sra

23,820
7
55
89

answered Jul 07 '09 at 21:32

Great answer. This works with MySQL as well where SqlFunctions.IsNumeric is MSSQL specific. – Derrick Dec 11 '14 at 04:40

score 0 · Answer 5 · answered Feb 17 '09 at 18:15

0

Look into Int32.TryParse ... That may work for you.

Ian

answered Feb 17 '09 at 18:15

Ian P

12,840
6
48
70

score -1 · Answer 6 · answered Feb 17 '09 at 18:27

You will not be able to do this with LINQ2SQL without loading all records on the client.

I can suggest to create view or table-valued function with aditional calculated column and then filter your results with this column. Or execute regular SQL with DataContext.ExecuteQuery.

Leandro López · Answer 7 · 2009-02-17T18:38:44.430

(Edited to suit your needs)

So, given the fact that you cannot use Integer.TryParse, the following is not feasible:

int i;
var rowsWithInts = (From r In dc.YourRows Where Integer.TryParse(r.Column, i));
var rowsWithoutInts = (From r In dc.YourRows Where !Integer.TryParse(r.Column, i));

Is it possible that you create a stored procedure and link it to the DataContext via the Methods Pane? In that case you can do in your SQL the following:

-- Rows with integers
SELECT * FROM your_table WHERE ISNUMERIC(varchar_column) = 1
-- Rows without integers
SELECT * FROM your_table WHERE ISNUMERIC(varchar_column) = 0

Does this helps?

This will load all records to the client. – Mike Chaliy Feb 17 '09 at 18:25 — Mike Chaliy, Feb 17 '09 at 18:25

How to know if a field is numeric in Linq To SQL

7 Answers7

Linked