CODEWITHSUNDEEP
mysql
David542
David542

Reputation: 110083

How to get largest number in SQL

I have a code VARCHAR field that I want to auto-increment if the user doesn't provide a code of their own. For example:

`code`
- '5'
- '8'
- 's987'
- '981A'

In this case, the largest (pure) number would be 8, so I want to autoincrement to 9. How would I do this? So, I want to:

(1) Get the largest number (even though it's stored as a VARCHAR) that is currently in my table. In the above entries, 8 would be the largest number, since s987 and 981A are not valid numbers. (2) Add one to this number to get the new autoincrement number.

Upvotes: 0

Views: 68

Answers (2)

Zafar Malik
Zafar Malik

Reputation: 6844

Try it-

SELECT MAX(code)+1 FROM table_name WHERE code REGEXP '^[[:alnum:]]+$' AND code*1>0;

Upvotes: 0

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

try this

SELECT max(CAST(code AS UNSIGNED))+1 FROM myTable WHERE code REGEXP '[0-9]+';

Upvotes: 1

Related Questions