How to create an auto incrementing string?

Question

I facing an issue to increment an string with 01, 02, ..., 10, 12

I have a SQL query which is giving me AAAA06 which is max in my database.
Now I have an requirement that, whenever user call this one, it will check the database with max no. which currently is AAAA06(is the max currently) and should return or print AAAA07.
Similarly when the max is AAAA09 then it should increment to AAAA10 and so on
As a language I am using Java but I want to know can I achieve this with my sql query it self or have to write Java code to achieve this?

My query I am using is like

select max(code) from mt_users where maincode='AAAA'

Calling the max no is done by me; I just want to increment or concatenate whatever it suits to get the desire output.

It returns me AAAA06 as this one is the max currently.

Important points

When initially it is AAAA the it should show AAAA01
When it is AAAA09 it should show AAAA10
when it is AAAA99 it will show AAAA100

Note: currently I have max as AAAA06 but for new user in future it can be AAAE so I have to start it with AAAE01.

You already seem to have the field mainode (btw, s that meant to be "mainnode" or "maincode"?) which contains the "AAAA" prefix. So can't you just have a numeric field that autoincrements at access and return a concatenation of both? — Thomas
– Thomas, Commented Feb 7, 2019 at 10:10
This kind of problem is symptomatic of poor (i.e. back-to-front) design. — Strawberry
– Strawberry, Commented Feb 7, 2019 at 10:14
@munish then you will have trouble finding max value. SELECT MAX(s) FROM (SELECT 'AAAA100' s UNION SELECT 'AAAA21') x returns AAAA21!!! — Salman Arshad
– Salman Arshad, Commented Feb 7, 2019 at 11:22

Salman Arshad · Accepted Answer · 2019-02-07 12:32:08Z

1

Short answer -- use this query:

SELECT id AS PrevID, CONCAT(
    SUBSTRING(id, 1, 4),
    IF(CAST(SUBSTRING(id, 5) AS UNSIGNED) <= 9, '0', ''),
    CAST(SUBSTRING(id, 5) AS UNSIGNED) + 1
) AS NextID
FROM (
    -- since you allow strings such as AAAA20 and AAAA100 you can no longer use MAX
    SELECT id
    FROM t
    ORDER BY SUBSTRING(id, 1, 4) DESC, CAST(SUBSTRING(id, 5) AS UNSIGNED) DESC
    LIMIT 1
) x

Results:

| PrevID  | NextID  | 
| AAAA100 | AAAA101 | 
| AAAA21  | AAAA22  | 
| AAAA06  | AAAA07  |

Just for the fun, I wrote this stored procedure that generates numbers that look like AAAA00 AAAA99 AAAB00 etc:

CREATE FUNCTION NextID(PrevID VARCHAR(6))
RETURNS VARCHAR(6)
BEGIN
    DECLARE s VARCHAR(4);
    DECLARE i INT;
    DECLARE j INT;

    SET s = LEFT(PrevID, 4);
    SET s = REPLACE(s, 'A', '0');
    SET s = REPLACE(s, 'B', '1');
    SET s = REPLACE(s, 'C', '2');
    SET s = REPLACE(s, 'D', '3');
    SET s = REPLACE(s, 'E', '4');
    SET s = REPLACE(s, 'F', '5');
    SET s = REPLACE(s, 'G', '6');
    SET s = REPLACE(s, 'H', '7');
    SET s = REPLACE(s, 'I', '8');
    SET s = REPLACE(s, 'J', '9');
    SET s = REPLACE(s, 'K', 'A');
    SET s = REPLACE(s, 'L', 'B');
    SET s = REPLACE(s, 'M', 'C');
    SET s = REPLACE(s, 'N', 'D');
    SET s = REPLACE(s, 'O', 'E');
    SET s = REPLACE(s, 'P', 'F');
    SET s = REPLACE(s, 'Q', 'G');
    SET s = REPLACE(s, 'R', 'H');
    SET s = REPLACE(s, 'S', 'I');
    SET s = REPLACE(s, 'T', 'J');
    SET s = REPLACE(s, 'U', 'K');
    SET s = REPLACE(s, 'V', 'L');
    SET s = REPLACE(s, 'W', 'M');
    SET s = REPLACE(s, 'X', 'N');
    SET s = REPLACE(s, 'Y', 'O');
    SET s = REPLACE(s, 'Z', 'P');

    SET i = RIGHT(PrevID, 2);
    SET j = CONV(s, 26, 10);

    SET i = i + 1;
    IF i > 99 THEN
        SET i = 0;
        SET j = j + 1;
    END IF;

    SET s = CONV(j, 10, 26);
    SET s = REPLACE(s, 'P', 'Z');
    SET s = REPLACE(s, 'O', 'Y');
    SET s = REPLACE(s, 'N', 'X');
    SET s = REPLACE(s, 'M', 'W');
    SET s = REPLACE(s, 'L', 'V');
    SET s = REPLACE(s, 'K', 'U');
    SET s = REPLACE(s, 'J', 'T');
    SET s = REPLACE(s, 'I', 'S');
    SET s = REPLACE(s, 'H', 'R');
    SET s = REPLACE(s, 'G', 'Q');
    SET s = REPLACE(s, 'F', 'P');
    SET s = REPLACE(s, 'E', 'O');
    SET s = REPLACE(s, 'D', 'N');
    SET s = REPLACE(s, 'C', 'M');
    SET s = REPLACE(s, 'B', 'L');
    SET s = REPLACE(s, 'A', 'K');
    SET s = REPLACE(s, '9', 'J');
    SET s = REPLACE(s, '8', 'I');
    SET s = REPLACE(s, '7', 'H');
    SET s = REPLACE(s, '6', 'G');
    SET s = REPLACE(s, '5', 'F');
    SET s = REPLACE(s, '4', 'E');
    SET s = REPLACE(s, '3', 'D');
    SET s = REPLACE(s, '2', 'C');
    SET s = REPLACE(s, '1', 'B');
    SET s = REPLACE(s, '0', 'A');

    RETURN CONCAT(LPAD(s, 4, 'A'), LPAD(i, 2, '0'));
END

SELECT NextID('AAAA01') -- AAAA02
SELECT NextID('AAAA99') -- AAAB00
SELECT NextID('AAAB99') -- AAAC00
SELECT NextID('AAAZ99') -- AABA00

edited Feb 7, 2019 at 12:32

answered Feb 7, 2019 at 12:24

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Salman Arshad Over a year ago

id/PrevID is the column that contains the string. NextID is the "generated" id based on previous id (added for demonstration).

manish thakur Over a year ago

hey when there is BBBB it is going to give BBBB01 ?

manish thakur Over a year ago

hey sir i have a very small issue..whenever you ll be free please comment here

Barbaros Özhan · Accepted Answer · 2019-02-07 12:39:25Z

1

You can use lpad as

select case when code < 10 
            then concat( substring(maincode,1,4),lpad( max(code)+1 ,length(code+10),'0')) 
            else concat( substring(maincode,1,4),lpad( max(code)+1 ,length(code+1),'0')) 
            end
    as "Result String"
  from tab
 where maincode = 'AAAA01' -- 'AAAE';

Depending on the chat among us, you need the following logic :

select concat( substring(maincode,1,4), 
                     case when substring(maincode,5,length(maincode)-4) !=
                               substring(maincode,5,length(maincode)-4)+1
                     then
                         substring(maincode,5,length(maincode)-4)+1
                     else
                      lpad( 
                         substring(maincode,5,length(maincode)-4)+1,
                      length(maincode)-4,'0')
                     end
                )               
    as "Result String"
  from tab;

Demo

edited Feb 7, 2019 at 12:39

answered Feb 7, 2019 at 10:26

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

9 Comments

manish thakur Over a year ago

hey sir it is giving wrong result as for AAAA06 it is it is giving AAAA0601

Barbaros Özhan Over a year ago

@manishthakur are you sure dear friend, consider the demo please.

manish thakur Over a year ago

let me check sir

manish thakur Over a year ago

hey sir for AAAA it is giving AAAA01 but for AAAA01 it is giving null as in where clause maincode=AAAA01 it display null,you can check

manish thakur Over a year ago

same issue sir giving AAAA1 insted of AAAA01 and for AAAA01 it is giving null

|

Collectives™ on Stack Overflow

How to create an auto incrementing string?

2 Answers 2

3 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related