SQL code to generate next sequence in a alphanumeric string

Question

I have some string values already populated in a nvarchar column. the format of the strings are like this:

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. Pick up the string with the largest number.
2. If multiple largest number, then pick up the string the largest alphabet among those.

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. The same number as the largest one, but alphabet incremented by 1. I.E. 128N
2. If the alphabet reaches to Z, then the number gets incremented by 1, and alphabet is A. for example, the next String of 128Z is 129A.

Can anyone let me know what kind of SQL can get me the desired string.

Answer 1

If you can change the table definition(*), keeping the basic values entirely numeric and just formatting into these strings would be easier:

create table T (
    CoreValue int not null,
    DisplayValue as CONVERT(varchar(10),(CoreValue / 26)+1) + CHAR(ASCII('A') + (CoreValue-1) % 26)
)
go
insert into T (CoreValue)
select ROW_NUMBER() OVER (ORDER BY so1.object_id)
from sys.objects so1,sys.objects so2
go
select * from T

Results:

CoreValue   DisplayValue
----------- ------------
1           1A
2           1B
3           1C
4           1D
5           1E
6           1F
....
22          1V
23          1W
24          1X
25          1Y
26          2Z
27          2A
28          2B
29          2C
....
9593        369Y
9594        370Z
9595        370A
9596        370B
9597        370C
9598        370D
9599        370E
9600        370F
9601        370G
9602        370H
9603        370I
9604        370J

So inserting a new value is as simple as taking the MAX from the column and adding 1 (assuming serializable isolation or similar, to deal with multiple users)

---

(*) Even if you can't change your table definition - I'd still generate this table. You can then join it to the original table and can use it to perform a simple MAX against an int column, then add one and look up the next alphanumeric value to be used. Just populate it with as many values as you ever expect to use.

Answer 2

Assuming:

CREATE TABLE MyTable
    ([Value] varchar(4))
;

INSERT INTO MyTable
    ([Value])
VALUES
    ('16B'),
    ('23G'),
    ('128F'),
    ('128M')
;

You can do:

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 Fiddle Example

Answer 3

Assuming your column always follows the format you described (number+1 char suffix), you can do

WITH cte1 AS(
SELECT LEFT(your_column,LEN(your_column)-1) as num, 
RIGHT(your_column,1) as suffix
FROM your_table),
cte2 AS (SELECT MAX(num) as max_num FROM cte1)
SELECT 
 CASE c.max_suffix
  WHEN 'Z' THEN 'A'
  ELSE NCHAR(UNICODE(c.max_suffix)+1)
 END as next_suffix,

CASE c.max_suffix
 WHEN 'Z' THEN a.max_num+1
 ELSE a.max_num
END as next_num

FROM  cte2 a 
CROSS APPLY (SELECT MAX(suffix) as max_suffix FROM cte1 b WHERE b.num=a.max_num)c
;

I'm pretty sure there are other ways to do the same; also, my approach doesn't seem optimal, but I think it returns what you need...

No doubt it would be much better if you can redesign the table as Damien_The_Unbeliever recommends.

Answer 4

To generate alphanumeric String sequence in below format. A B C.....Y Z AA AB......AZ BA BB.....BZ...go on.

CREATE OR REPLACE FUNCTION to_az(in_num  number)

RETURN VARCHAR2

IS

 num          PLS_INTEGER    := TRUNC (in_num) - 1;
 return_txt     VARCHAR2 (1) := CHR (65 + MOD (num, 26));

BEGIN

 IF  num <= 25
 THEN
      RETURN     return_txt;
 ELSE
      RETURN     to_az (FLOOR (num / 26))
           || return_txt;
 END IF;
END to_az;

Answer 5

Ms-sql function to generate an alpha-numeric next sequence id like 'P0001','P0002' and so on.

ALTER FUNCTION NextProductID()
 returns varchar(20)
 BEGIN
    DECLARE  @NEXTNUMBER INT;
    DECLARE @NEXTPRODUCTID VARCHAR(20);
 SELECT @NEXTNUMBER=MAX( CONVERT(INT, SUBSTRING(PRODUCT_CODE,2,LEN(PRODUCT_CODE))))+1 FROM Product;
    --PRINT @NEXTNUMBER;
    SET @NEXTPRODUCTID=CONVERT(VARCHAR,@NEXTNUMBER)
    SELECT @NEXTPRODUCTID='P'+REPLICATE('0',6-LEN(@NEXTPRODUCTID)) + @NEXTPRODUCTID;
  return @NEXTPRODUCTID;
END

Here product is the table name and product_code is the column