string 在 VARCHAR 字段中使用 MAX()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6353427/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using MAX() in VARCHAR Field
提问by Vivek
I have a table with following set of data
我有一个包含以下数据集的表格
ID (VARCHAR2 field)
D001
D002
D010
D0012
I use max()in this field.
我max()在这个领域使用。
Select max(ID) from <table-name>;
It returns D010as result.
它D010作为结果返回。
Why is the result not D0012?
为什么结果不是D0012？
回答by Thomas
You get D010because alphabetically, D010comes after D0012or said another way, D01comes after D00and therefore anything that is D01xcomes after anything that starts D00x.
你得到，D010因为按字母顺序，D010在之后D0012或以另一种方式说，D01在之后D00，因此D01x在任何开始的之后出现D00x。
回答by Santhosh
below code is working for me as per your expectation
下面的代码按照您的期望对我有用
select max(to_number(regexp_substr(id, '\d+'))) id from <yourtable>;
回答by Ketan Dubey
This will surely work.
这肯定会奏效。
select MAX(CAST(REPLACE(REPLACE(ID, 'D', ''), '', '') as int)) from <table-name>
回答by BSB
First Varchar need to Casted as int to select as MAX. Use below query:
首先 Varchar 需要 Casted as int 才能选择为 MAX。使用以下查询：
select max(CAST(ID as signed)) as max_id from <table-name>;
select max(CAST(ID as signed)) as max_id from <table-name>;
回答by Muhammad ali
SELECT *
FROM <TABLE_NAME>ORDER BY CAST( IDAS DECIMAL( 10, 3 ) ) DESC
SELECT * FROM <TABLE_NAME>ORDER BY CAST( IDAS DECIMAL(10, 3)) DESC
回答by Mohamed Islam Fares
this should work
这应该有效
Select MAX(ID) from table where IsNumeric(ID) = 1 ;
