CODEWITHSUNDEEP
mysql
user10705797
user10705797

Reputation:

How to auto increment a string with sql query

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

This is the data into my db

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

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

Upvotes: 1

Views: 195

Answers (1)

Aishwarya
Aishwarya

Reputation: 433

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

Upvotes: 1

Related Questions