假设:
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