sql 如何在字母和数值中找最大值？

Question

有一表A。
 ID
1000
1001
1002
1003
40001
20002
30003
P10001
P10002
P10003
P10004
P10005如何在能从这个ID字段里找到1003这个值？    sql = "SELECT MAX(ID) AS 编号 FROM 表A WHERE cast(ID as integer) BETWEEN 1000 AND 9999"  '我用以上查询却因为有P10001这些带字母而出错，求解答！！！

Answer 1

要看什么数据库，如果是 SQL Server，可以在 SQL Server 里写数字过滤函数直接用到 SQL 语句里，
如果是 Access 就不好弄了。

Answer 2

sql = "Select Max(ID) From 表A Where ID Between 1000 And 9999"

Answer 3

不行，提示错误，“将varchar值"P10001”转换为数据类型为int的列时发生语法错误”
sql = "SELECT MAX(ID) AS 编号 FROM 表A WHERE cast(ID as integer) BETWEEN 1000 AND 9999"
我用以上查询却因为有P10001这些带字母而出错.

Answer 4

Select Max(ID) From 表A Where ID Between '1000' And '9999'

Answer 5

如果确认P在5位数字之前：
Sql = "Select Max(Right(ID,5)*1) From 表A Where Right(ID,5)*1 Between 1000 And 9999"

Answer 6

先在SQL里添加标量值函数，然后放入以下函数代码ALTER FUNCTION [dbo].[GetStrNumber](@strRS varchar(1000)='') RETURNS integer
AS
BEGIN
DECLARE @IncNum integer
DECLARE @intStrSize integer
DECLARE @intReadCount integer
DECLARE @strTemp varchar(1000)
DECLARE @strRetStr varchar(1000)
SET @intStrSize=Len(@strRS)
SET @intReadCount=1
SET @strRetStr=''
WHILE @intReadCount<=@intStrSize
BEGIN
SET @strTemp = Substring(@strRS,@intReadCount,1)
IF Ascii(@strTemp)>=Ascii('0') AND Ascii(@strTemp)<=Ascii('9')
SET @strRetStr = @strRetStr+@strTemp
SET @intReadCount = @intReadCount + 1
END
IF Len(@strRetStr)>0
SET @IncNum = @strRetStr
ELSE
SET @IncNum = 0
RETURN (@IncNum)
END
go选好函数执行的数据库，然后执行函数过程,成功后函数会加到"标量值函数"里
在查询的时候这样输入查询SELECT MAX(dbo.GetStrNumber(ID)) AS MaxID FROM 表

Answer 7

Sql = "Select Max(Mid(ID,iif(Left(ID,1)='P',2,1),10)) From 表A Where Mid(ID,iif(Left(ID,1)='P',2,1),10) Between 1000 And 9999"

Answer 8

SELECT MAX([ID])
FROM TBTest 
WHERE CASE WHEN ASCII([ID])<58 THEN [ID] ELSE 0 END BETWEEN 1000 AND 9000  

Answer 9

SELECT MAX([ID])
FROM A 
WHERE CASE WHEN ASCII([ID])<58 THEN [ID] ELSE 0 END BETWEEN 1000 AND 9000  

Answer 10

USE testIF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES
      WHERE table_name = 'TBTest')
   DROP TABLE TBTest
GOCREATE TABLE TBTest
(
    [ID] varchar(10)
)
GOINSERT INTO TBTest ([ID]) VALUES ('1000');
INSERT INTO TBTest ([ID]) VALUES ('1001');
INSERT INTO TBTest ([ID]) VALUES ('1002');
INSERT INTO TBTest ([ID]) VALUES ('1003');
INSERT INTO TBTest ([ID]) VALUES ('40001');
INSERT INTO TBTest ([ID]) VALUES ('20002');
INSERT INTO TBTest ([ID]) VALUES ('30003');
INSERT INTO TBTest ([ID]) VALUES ('P10001');
INSERT INTO TBTest ([ID]) VALUES ('P10002');
INSERT INTO TBTest ([ID]) VALUES ('P10003');
INSERT INTO TBTest ([ID]) VALUES ('P10004');
INSERT INTO TBTest ([ID]) VALUES ('P10005');
GO
SELECT MAX([ID])
FROM TBTest 
WHERE CASE WHEN ASCII([ID])<58 THEN CAST([ID] AS INT) ELSE 0 END BETWEEN 1000 AND 9000  
GO-- 结果：1003

Answer 11

谢谢各解答，最后用下面的嵌套行了，但是搞不懂呀。
sql = "Select MAX(ID) From (Select * From 表A Where ID Between '1000' And '9999') A Where ID Between 1000 And 9999"sql = "Select MAX(ID) From (Select * From 表A Where ID Between '1000' And '9999') A Where ID Between 1000 And 9999"红色部分得以下结果，去除了带字母部分的项，但不在1000至9999之间，超出9999值的出查询出来了，为什么？？？？？？？？？？？？
1000
1001
1002
1003
40001
20002
30003

Answer 12

其实Select * From 表A Where ID Between '1' And '9' 也是那个结果。
正如16F所说，是按字符排列的；
如果是类似'100abc',也会被查到；实际上确实是需要逐个字符判断字段内容的。
如果没有这种数据，那个查找的结果也算正确吧。

Answer 13

sql = "Select MAX(ID) From  表A Where ID Between '1000' And '9999' And lendth(id)=4 "

Answer 14

sql server也支持isnumeric :SELECT MAX([ID])
FROM TBTest 
WHERE CASE ISNUMERIC([ID]) WHEN 1 THEN CAST([ID] AS INT) ELSE 0 END BETWEEN 1000 AND 9000  

Answer 15

SELECT MAX([ID])
FROM TBTest 
WHERE CASE ISNUMERIC([ID]) WHEN 1 THEN CAST([ID] AS INT) ELSE 0 END BETWEEN 1000 AND 9000