Am having a lot of transaction number store in my db, example I-0001, IN-0001, N-0001, S-0001, SN-0001, R-0001 , R-AD-0001, and R-AA-00001.
Every time a new row insert, I will select the maximum number of transaction numbers and + 1 to keep track of every transaction number.
Here is my initial SQL SELECT MAX(`transaction_invoice`) FROM sw_invoice WHERE `transaction_invoice` LIKE '%N-%', but I found out using LIKE to select for example maximum number of N-, LIKE SQL will also select N-0001 and SN-0001 together
Example of LIKE SQL result
To solve this I found out REGEXP might solve my issue here is example SQL
SELECT * FROM `sw_invoice` WHERE `transaction_invoice` REGEXP '^N-', this SQL works well when select maximum number of N-, but when its come to select R-0001, it will also select R-AD-0001, and R-AA-00001 which start with R-
Example of REGEXP SQL result
Is there a way any SQL can only select the maximum transaction number I require? for example if I select transaction number start with R-, its only return the maximum number of R- not R-AD-
Just remove the leading wildcard from the like:
SELECT MAX(`transaction_invoice`)
FROM sw_invoice
WHERE `transaction_invoice` LIKE 'N-%';
You don't need regular expressions for this.
If you need to handle hyphens in the first part, then you need regular expressions:
where transaction_invoice regexp '^N-[0-9]+$'