Help me to solve.. thankx is advance...

I'm using mysql 4.23.56 max-log. I have done a project with mysql as Back-end. Around 2 lakhs records are there there in my database. But now a days in the MAX() queries -which used to generate the ID- frequently I'm getting Invalid values. That means a big number than what the original Max value is.
The Query that I'm useing is :

SELECT MAX(TRUNCATE(MID('BillNo',2 ,LENGTH('BillNo')) , 0)) AS MAXID FROM Sales_M WHERE BillNo LIKE 'R/%'

Here ,
BillNo is Varchar(20),
R/ indicates RetailSales Bills
W/ indicates WoleSales Bills

My database is running in Red Hat Linux 8. What extra should I do..?
I want a permanent solution...!

Im thanking u to spent time to help me...

What is the type of the field in which you are inserting the MAXID into?

Don't quite get this:
MID('BillNo',2 ,LENGTH('BillNo')
That says you are looking in the field 'BillNo' from the second character for a string the length of the BillNo field. If you want from position 2 to the end, then just use MID('BillNo', 2). Maybe you're picking up some crap from the end of the string (shouldn't happen, but...)

The TRUNCATE() looks a bit irrelevant as well, unless your data has white space at the end. Can you give some examples of the data in BillNo? That said, maybe you should just change the WHERE clause to WHERE 'BillNo' LIKE 'R/%'

I'm sorry to inform that I'm usung the query like:

SELECT MAX(TRUNCATE(MID(BillNo,2 ,LENGTH(BillNo)) , 0)) AS MAXID FROM Sales_M WHERE BillNo LIKE 'R/%

insted of

SELECT MAX(TRUNCATE(MID('BillNo',2 ,LENGTH('BillNo')) , 0)) AS MAXID FROM Sales_M WHERE BillNo LIKE 'R/%
--------------------------------
the Example values look like

SELECT BillNo FROM Sales_M WHERE MID(BillNo,2 ,LENGTH(BillNo)) Between 6704 And MID(BillNo,2 ,LENGTH(BillNo))
6704

BillNo
-----------
R/6704
W/6704
R/6705
W/6705
R/6706
W/6706
----------------

Thank you to reply me..

Try this:

SELECT MAX(RIGHT(TRIM('BillNo'), LENGTH('BillNo')-2)) AS MAXID FROM Sales_M

You want TRIM() rather than TRUNCATE() (I assume - TRUNCATE is for deleting table contents!) If you need to TRIM anything, you should do it before you work on the string.

I have also changed your use of MID() to RIGHT(), since you're looking for everything to the right of the first two characters. You also don't need a WHERE clause if you're looking across all rows.

Hi thanks a lot ..

I will try and come back soon...

thanks
Dibu John