SQL query to select max value of a varchar field

Question

I want to select the max value of a varchar field and increment its value by 1 retaining its original format ..

Customer ID for ex. : CUS0000001 is the value

add 1 to it and then enter it into the database along with other details.

So the result should be like CUS0000002 ..

This is what I have tried and in fact achieved what I want ..

But is this the best way to do it ??

SELECT 
   CONCAT('CUS', RIGHT(CONCAT('000000', CONVERT((CONVERT(MAX(RIGHT(customer_id, 7)) , UNSIGNED) + 1), CHAR(10))), 7)) AS customer_id 
FROM customer_master

Answer 1

I agree with the commenters that the database should not be designed like this. However, if you are stuck with it, I suggest something like this to get the highest value:

select customer_id from customer_master 
    order by customer_id desc 
    fetch first row only;

Then generate the new value by performing your logic on the result that is returned. This will be much faster, because the substring operations will be quite slow, and in this case they will only be performed once.

(This assumes that every ID will have the same prefix, namely 'CUS'; however, your code seems to imply that).