T-SQL IsNumeric() and Linq-to-SQL

isnumeric, linq-to-sql, sql-server, t-sql

Solution

Although `ISNUMERIC` is missing, you could always try the nearly equivalent `NOT LIKE '%[^0-9]%`, i.e., there is no non-digit in the string, or alternatively, the string is empty or consists only of digits:

from x in table 
where SqlMethods.Like(x.col, 'EU[0-9]%') // starts with EU and at least one digit
  && !SqlMethods.Like(x.col, '__%[^0-9]%') // and no non-digits
select x;

Of course, if you know that the number of digits is fixed, this can be simplified to

from x in table 
where SqlMethods.Like(x.col, 'EU[0-9][0-9][0-9][0-9][0-9][0-9]')
select x;

Problem

I need to find the highest value from the database that satisfies a certain formatting convention. Specifically, I would like to find the highest value that looks like EU999999 ('9' being any digit) select max(col) will return something like 'EUZ...' for instance that I want to exclude. The following query does the trick, but I can't produce this via Linq-to-SQL. There seems to be no translation for the isnumeric() function in SQL Server. ``` select max(col) from table where col like 'EU%' and 1=isnumeric(replace(col, 'EU', '')) ``` Writing a database function, stored procedure, or anything else of that nature is far down the list of my preferred solutions, because this table is central to my app and I cannot easily replace the table object with something else. What's the next-best solution?

Original source