[SOLVED] Order a text/number string in MySQL?

[Gibbs]

I have values like this in my database:

VTXH001/80
VTXD002/95
VTXD003/95

VTX > Letter of second name > Issue Number > Result. So if your name is Mr Smith and you scored 60 it would be VTXS004/60

My problem is that I need to order them by the 001/002/003 (etc) part. Is it possible to search through the whole result and order it by a certain part?

I hope that makes sense and thanks for any help.

[toplay]

When you think that data needs to be sorted by something, it's always a good idea to have a separate column for that.

Anyway, here's a quick and dirty example that should work provided you always have the Issue Number start in position 5 for a length of 3:

SELECT
            SUBSTR(`column_name`, 5, 3) + 0 AS issue_number
          , tb.*
  FROM
          `table_name` tb
WHERE
            ....some condition...
ORDER BY
            issue_number ASC
;


See manual page:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

[Gibbs]

SUBSTR didn't work but it worked perfect with SUBSTRING. Thank you!