How to auto increment a string with sql query

Question

I am stuck at a point where i have to increment a string, and my strings are of type C001,SC001,B001

in my data base they are defined like

what i am trying to do do is write a query which check the previous highest code present into my db and the incriment it to +1

for example C001 -> C002,C009->C010,C099`->C100 and so on
Similarly for SC001->SC002,SC009->SC010,SC099->SC100 and so on
Similarly fro B001 -> B002,B009->B010,B099`->B100 and so on

I have a query which my friend has suggested me to use but that query only incriminating AAAA->AAAA01 , AAAA09->AAAA10

query is

    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

when i am replacing ID with CategoryCode it is giving me PrevID-C004 NextID-C00401 which is not my requirement i want PrevID-C004 and NextID->C005

NOTE i am using my sqlServer 5.1

You can't easily do this on MySQL 5.x. Question: What happens when a record, e.g. C003, gets deleted? Do you recycle that ID? — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 14, 2019 at 6:26
@TimBiegeleisen there is no scoop currently for deletion i just have to add new row and the above query i have posted i am using mysql 5x only — user10705797
– user10705797, Commented Feb 14, 2019 at 6:34
@viveksingh if the record goes more than hundreds then it will be static as sc999 — Aishwarya
– Aishwarya, Commented Feb 14, 2019 at 7:16
@Aishwarya this one is never gona happen because.it can go max to max 300-400 — user10705797
– user10705797, Commented Feb 14, 2019 at 7:22
This kind of problem is symptomatic of poor design - a confusion about the kind of information the database needs in order to relate one piece of data to another , and the kind of thing you want to present to an end user. — Strawberry
– Strawberry, Commented Feb 14, 2019 at 8:07

Aishwarya · Accepted Answer · 2019-02-14 08:01:21Z

1

Just try this one ,

SELECT 
CategoryCode,CAST(CONCAT(LPAD(CategoryCode,1,0),LPAD(MAX(RIGHT(CategoryCode, 
3)) + 1, 3, 0) ) AS CHAR),
FROM  test

SELECT 
SubCategoryCode,CAST(CONCAT(LPAD(SubCategoryCode,2,0), 
LPAD(MAX(RIGHT(CategoryCode, 3)) + 1, 3, 0) ) AS CHAR), 
FROM  test

SELECT  
BrandCode,CAST(CONCAT(LPAD(BrandCode,1,0), LPAD(MAX(RIGHT(BrandCode, 3)) + 
1, 3, 0))  AS CHAR) FROM  test

edited Feb 14, 2019 at 8:01

answered Feb 14, 2019 at 7:38

Aishwarya

4333 silver badges10 bronze badges

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

1 Comment

user10705797 Over a year ago

hey you are doing it in one query only.it is giving fine result..but i have to write 3 separate query for that.as i have to use all three separately

Collectives™ on Stack Overflow

How to auto increment a string with sql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related