问题描述
我已经在 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 小提琴示例