<div>
If the format is consistent with the same letter 'R' followed by 3 numbers, then you can use 'ORDER BY' that column 'DESC' with a 'LIMIT' of 1 to get the highest value in the table and remove the 'R' to get only the number.
</div>


If the format is consistent with the same letter 'R' followed by 3 numbers, then you can use 'ORDER BY' that column 'DESC' with a 'LIMIT' of 1 to get the highest value in the table and remove the 'R' to get only the number.

<div>
You could use this<br />
<br />
select max(substr(col,2)*1)+1 from tbl<br />
<br />
Assuming the number starts from position 2 in the string.<br />
So R0001, R0101, R1010, R99 etc<br />
<br />
*1 is used to convert to number, so that &quot;1010&quot; &gt;&nbsp;&quot;99&quot;
</div>


You could use this

select max(substr(col,2)*1)+1 from tbl

Assuming the number starts from position 2 in the string.
So R0001, R0101, R1010, R99 etc

*1 is used to convert to number, so that "1010" > "99"

<div>
What if the alpha characters are not in a constant position?<br />
<br />
I can have R001, but I could also have RR001 or R001-R, or technically any combination thereof.<br />
<br />

</div>


What if the alpha characters are not in a constant position?

I can have R001, but I could also have RR001 or R001-R, or technically any combination thereof.



<div>
Does the table have a unique key?
</div>


<div>
<div class="content wysiwyg-content">
I have a field in a table with data in the format of:<br />
<br />
R100<br />
R101<br />
R102<br />
<br />
I need to return the maximum of the numeric part and increment by 1 to assist the user in setting a unique value that has not been used yet. I had thought regexp might work for me, but it does not seem to be the right fit - as it returns only a 1 or 0.<br />
<br />
Is there another way to do this? <br />
<br />

</div>
</div>


I have a field in a table with data in the format of:

R100
R101
R102

I need to return the maximum of the numeric part and increment by 1 to assist the user in setting a unique value that has not been used yet. I had thought regexp might work for me, but it does not seem to be the right fit - as it returns only a 1 or 0.

Is there another way to do this? 



Return numeric part from varchar field

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.