問題描述
我已經在 nvarchar 列中填充了一些字符串值.字符串的格式是這樣的:
I have some string values already populated in a nvarchar column. the format of the strings are like this:
例如:16B、23G、128F、128M等...
For example: 16B, 23G, 128F, 128M etc...
我需要從中找出最大值,然后從代碼中生成下一個.拾取最大項的邏輯如下:
I need to find out the maximum value from these, then generate the next one from code. The logic for picking up the maximum item is like the following:
- 選擇數字最大的字符串.
- 如果有多個最大的數字,則選擇其中最大的字母.
例如,上述系列中最大的字符串是 128M.
For example, the largest string from the above series is 128M.
現在我需要生成下一個序列.下一個字符串將有
Now I need to generate the next sequence. the next string will have
- 與最大的數字相同,但字母表增加了 1.I.E.128N
- 如果字母達到 Z,則數字增加 1,字母為 A.例如,128Z 的下一個字符串是 129A.
誰能告訴我什么樣的 SQL 可以得到我想要的字符串.
Can anyone let me know what kind of SQL can get me the desired string.
推薦答案
假設:
CREATE TABLE MyTable
([Value] varchar(4))
;
INSERT INTO MyTable
([Value])
VALUES
('16B'),
('23G'),
('128F'),
('128M')
;
你可以這樣做:
select top 1
case when SequenceChar = 'Z' then
cast((SequenceNum + 1) as varchar) + 'A'
else
cast(SequenceNum as varchar) + char(ascii(SequenceChar) + 1)
end as NextSequence
from (
select Value,
cast(substring(Value, 1, CharIndex - 1) as int) as SequenceNum,
substring(Value, CharIndex, len(Value)) as SequenceChar
from (
select Value, patindex('%[A-Z]%', Value) as CharIndex
from MyTable
) a
) b
order by SequenceNum desc, SequenceChar desc
SQL 小提琴示例
這篇關于用于生成字母數字字符串中的下一個序列的 SQL 代碼的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網!