1,216
社区成员
发帖
与我相关
我的任务
分享
USE test
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'TBTest')
DROP TABLE TBTest
GO
CREATE TABLE TBTest
(
[ID] varchar(10)
)
GO
INSERT 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
SELECT MAX([ID])
FROM A
WHERE CASE WHEN ASCII([ID])<58 THEN [ID] ELSE 0 END BETWEEN 1000 AND 9000
SELECT MAX([ID])
FROM TBTest
WHERE CASE WHEN ASCII([ID])<58 THEN [ID] ELSE 0 END BETWEEN 1000 AND 9000
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 表
SELECT MAX([ID])
FROM TBTest
WHERE CASE ISNUMERIC([ID]) WHEN 1 THEN CAST([ID] AS INT) ELSE 0 END BETWEEN 1000 AND 9000
SELECT MAX([ID])
FROM TBTest
WHERE CASE ISNUMERIC([ID]) WHEN 1 THEN CAST([ID] AS INT) ELSE 0 END BETWEEN 1000 AND 9000