从Sql中的字母数字列中检索最大数量 [英] Retrieve maximum number from alphanumeric column in Sql
问题描述
请有人帮我查询SQL查询如何从字母数字字段中获取最大数量。以下是列值。如您所见,我想在下面的值中检索OSS120的最大数字。前3个字母总是不变的。提前致谢。
ASG1
ASG10
ASG14
ASG2
ASG21
ASG4
LTS1
LTS10
LTS14
LTS2
LTS21
LTS4
OSS1
OSS10
OSS100
OSS11
OSS114
OSS120
OSS2
OSS21
OSS3
OSS4
OSS5
OSS6
OSS7
这很容易做到。我在评论中给了你一个线索,并希望你能用它而不是要求更多。你会以这种方式学到更多东西。但是,请参阅以下内容:
SELECT MAX( CONVERT ( INT , RIGHT (MailRefNo,LEN(MailRefNo) -3)))
FROM MailRegistry
RIGHT( MailRefNo,LEN(MailRefNo)-3)) - 简单地隔离数字,删除前3个字符,因为你说前3个字符总是alpha字符。
然后将其转换为INT并获得MAX。
可能是这样的?
DECLARE @数据 表(n VARCHAR ( 10 ))
INSERT @ Data (n)
VALUES (' ASG1')
,(' ASG12')
,(' ASG54')
,(' ASGa4')
; WITH CTE_Test AS (
SELECT
value,
CASE
WHEN ISNUMERIC(SUBSTRING(值) , 4 , 20 ))= 1
那么 CAST(SUBSTRING(值, 4 , 20 ) AS INT )
ELSE 0
END as number
FROM
@ Data
WHERE
LEN(value)> 3
)
SELECT TOP 1 值
FROM CTE_Test
ORDER BY number DESC ,
value ASC 跨度>
Please can someone help me with SQL query how to get maximum number from an alphanumeric field. Below is the column values. As you can see I want to retrieve the highest number as OSS120 in the below values. The first 3 letters are always constant. Thanks in advance.
ASG1
ASG10
ASG14
ASG2
ASG21
ASG4
LTS1
LTS10
LTS14
LTS2
LTS21
LTS4
OSS1
OSS10
OSS100
OSS11
OSS114
OSS120
OSS2
OSS21
OSS3
OSS4
OSS5
OSS6
OSS7
This is pretty easy to do. I gave you a clue in the comments and wish you would have gone with that instead of asking for more. You would have learned more that way. However, see below:
SELECT MAX(CONVERT(INT, RIGHT(MailRefNo, LEN(MailRefNo)-3))) FROM MailRegistry
RIGHT(MailRefNo, LEN(MailRefNo)-3)) - simply isolates the number, removing the first 3 characters since you said the first 3 characters are always alpha characteres.
Then convert that to an INT and get the MAX.
maybe something like this?
DECLARE @Data TABLE(n VARCHAR(10)) INSERT @Data (n) VALUES ('ASG1') , ('ASG12') , ('ASG54') , ('ASGa4') ;WITH CTE_Test AS ( SELECT value, CASE WHEN ISNUMERIC(SUBSTRING(value,4,20)) = 1 THEN CAST(SUBSTRING(value,4,20) AS INT) ELSE 0 END as number FROM @Data WHERE LEN(value)> 3 ) SELECT TOP 1 value FROM CTE_Test ORDER BY number DESC, value ASC
这篇关于从Sql中的字母数字列中检索最大数量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!