用于从varchar字段获取MAX编号的SQL [英] SQL for getting MAX number from varchar field

问题描述

我有一个表格，其中有一个字段billNo作为varchar。表有两种数据。一种是非数字，另一种是数字类型。如下所示 -



表名： billinfo

字段和数据：

I have a table where has a field "billNo" as varchar. Table has two kinds of data. One kind is non number and other kind is number type. Like the following-

Table name: billinfo
Field and data:

billNo
--------- 
1004-0058
1004-0059
1004-0060
1004-0061
1004-0062
1004-0063
1004-0064
1004-0065
1004-0066
01
02
03
04
05
06
07
08

我需要一个sql从表billinfo中的字段billNo返回最大数字。记住 - 非数值不需要考虑。我需要的返回值是08.



请..

I need a sql what return max number from the field "billNo" from the table billinfo. Remember- The non number value no need to consider. I need the return value is 08.

Please ..

推荐答案

试试这个：

temp是表名，数字是列名

Try This :
temp is table name and number is column name

select max(Convert(int, number)) from temp where isnumeric(number) = 1

你真的不能这样做：SQL没有TryParse方法，它允许你尝试将字符串转换为数字，如果失败则继续。

你不能在字符串字段上使用MAX并获得数字结果，除非字符串长度相同并用前导零填充，因为字符串比较在第一个字符差异处停止并使用作为整个比较的基础。



最好的解决方案是为此使用两个不同的单独字段：一个基于sting，一个数字。没有像你所示的混合数据的好解决方案。

You can't really do that: SQL doesn't have a TryParse method which lets you attempt to convert a string to a number and continue if it fails.
And you can't use MAX on a string field and get numeric results unless the strings are all the same length and padded with leading zeros, as a string comparison stops at the first character difference and uses that as the basis for the whole comparison.

The best solution is to use two different separate fields for this: one sting based, and one numeric. There is no "nice" solution with mixed data such as you show.

试试这个 -

try this-

Select MAX( CAST(billNo as int)) from
billInfo
Where billNo NOT LIKE '%-%'

或

or

Select MAX(numberBillNo)
from (
SELECT Cast(billNo as int) as numberBillNo  FROM billInfo
Where billNo NOT LIKE '%-%'
) as tmp

这篇关于用于从varchar字段获取MAX编号的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！