用于生成字母数字字符串中的下一个序列的 SQL 代码

问题描述

我已经在 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:

1. 选择数字最大的字符串.
2. 如果有多个最大的数字，则选择其中最大的字母.

例如，上述系列中最大的字符串是 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. 与最大的数字相同，但字母表增加了 1.I.E.128N
2. 如果字母达到 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 小提琴示例