T-SQL IsNumeric()和Linq-to-SQL [英] T-SQL IsNumeric() and Linq-to-SQL

问题描述

我需要从满足某种格式约定的数据库中找到最高的值.具体来说，我想找到看起来像这样的最高值

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"为任意数字)

EU999999 ('9' being any digit)

例如，

select max(col)将返回类似"EUZ ..."的内容，但我要排除在外. 以下查询可以解决问题，但是我无法通过Linq-to-SQL产生此问题. SQL Server中的isnumeric()函数似乎没有翻译.

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?

推荐答案

尽管缺少ISNUMERIC，但您始终可以尝试使用几乎等价的NOT LIKE '%[^0-9]%，即字符串中没有非数字，或者，字符串为空或仅包含数字:

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;

这篇关于T-SQL IsNumeric()和Linq-to-SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！