SQL query to select max value of a varchar field <p>I want to select the max value of a varchar field and increment its value by 1 retaining its original format .. </p> <p>Customer ID for ex. : <code>CUS0000001</code> is the value </p> <p>add 1 to it and then enter it into the database along with other details.</p> <p>So the result should be like <code>CUS0000002</code> ..</p> <p>This is what I have tried and in fact achieved what I want .. </p> <p>But is this the best way to do it ?? </p> <pre><code>SELECT CONCAT('CUS', RIGHT(CONCAT('000000', CONVERT((CONVERT(MAX(RIGHT(customer_id, 7)) , UNSIGNED) + 1), CHAR(10))), 7)) AS customer_id FROM customer_master </code></pre>