从 varchar 列中选择最大整数

2021-09-10 00:00:00 tsql sql-server

我正在尝试从包含数字和字符串的 varchar 列中检索最大数字.我正在处理的数据示例:

I am trying to retrieve the largest number from a varchar column that includes both numbers and strings. An example of the data I'm working with:

箱号
123
A5
789
B1

BoxNumber
123
A5
789
B1

我需要从列中返回最大的数字(在本例中为 789)，同时忽略 A5 和 B1 的非数字值.

I need to return the largest number (789 in this case) from the column while ignoring the non-numeric values of A5 and B1.

我找到了使用自定义函数来解决问题的解决方案，但我需要一些可以在不依赖自定义函数或过程的情况下执行即席查询的东西.

I have found solutions that use custom functions to solve the problem, but I need something that can be executed ad-hoc query without relying on custom functions or procs.

推荐答案

你需要一个组合，因为下面的事情 isnumeric 返回 1

you need a combination because of the fact that isnumeric returns 1 for the following things

select isnumeric('+'),isnumeric('5d2')

你的 where 子句应该是这样的

your where clause would be like this

WHERE VALUE NOT LIKE '%[a-z]%'
        AND ISNUMERIC(VALUE) = 1

create table #bla (value varchar(50))
insert #bla values('123')
insert #bla values('a5')
insert #bla values('789')
insert #bla values('b1')

SELECT MAX(CAST(value AS Int)) FROM #bla
WHERE VALUE NOT LIKE '%[a-z]%'
    AND ISNUMERIC(VALUE) = 1

我在这里写了这个ISNUMERIC Trouble

相关文章