sorting varchar within MySQL

[Johnee]

Hi all.
I'm currently coding a DB-driven business management system and I'm almost done coding it (just a few minor changes to the code to make it work as intended).

Today I started filling up the database with fake data to see the code in action and I've found a bug which I think can be solved quite easily.

I've tried fixing it (and looked for a solution in the manual for quite some time) and came up with nothing (I guess it has to do something with the fact that I'm still a noob with SQL 🙂)

Haven't had much luck with finding any threads with a similar problem on this forum so here is the deal:

This is the column inside a database 'invoices':
invoice_num VARCHAR(10) NOT NULL
and it holds the number of the invoice. I've set it as VARCHAR since it has to hold two types of invoices. The first one is numeric (1, 2, 99, 319, ...) and the other one has 'd-' before the numeric part (d-1, d-2, d-100, d-283, ...). I would have used INT for the column type but as you can see I can't because 'd-' wouldn't be 'd-' anymore.

This is the code for extracting invoices from the database:

$year = 2004;
$month = 06;
$query = mysql_query("SELECT * FROM invoices WHERE YEAR(date) = '$year' AND MONTH(date) = '$month' ORDER BY invoice_num ASC");

Everything goes OK except for one part. Invoices don't ascend correctly. Here's how they go:
1
10
11
12
...
19
2
20
21
...

I have come up with a solution but I haven't found anything to make it work. So here's what I would like someone to show me:
How to 'expand' invoice_num values so they'd be 10 digits long filled with zeroes (0000000001, 0000000002, ...) and then send out the results in an ascending order without modifing the original invoice_num values? How could I do the same for the 'd-' type invoices (d-0000000001, d-0000000002, ...)?

Is there another way to order invoices correctly while preserving varchar column type?

Anything on this subject would be really helpful and I thank you in forward.

Cheers...

[nemonoman]

Easiest would be to save the number formatting you desire at the time you save the invoice.

You could update the table's current values this way:

LPAD(str,len,padstr)
Returns the string str, left-padded with the string padstr until str is len characters long. If str is longer than len' then it will be shortened to len characters.

UPDATE mytable
SET invoicenum=LPAD(invoicenum, 10, '0')
WHERE INSTR(invoicenum,'d')=0

UPDATE mytable
SET invoicenum=CONCAT('d-', LPAD(substring(invoicenum,2,8), 8, '0'))
WHERE INSTR(invoicenum,'d')>0

You could also select the invoicenums using these values in a formatting SELECT statement. Can you see how? Here's a hint:

SELECT IF(INSTR(invoicenum,'d')>0,

I leave the rest to you