views:

188

answers:

1

Hi,

I'm running a select that returns alphanumeric results, e.g:

ABC-1
ABC-2
ABC-10
SAM-1
SAM-2
SAM-10
SAM-20

I've tried using:

ORDER BY CAST(mid(field_name, 6, LENGTH(class) -5) AS unsigned)

and

ORDER BY filed_name + 0 ASC

this has helped put some order but I cant seem to order -2 before -10

many thanks

+1  A: 

How about

ORDER BY 
  LEFT(field_name, INSTR(field_name, '-') - 1),
  CAST(
    SUBSTRING(field_name, INSTR(field_name, '-') + 1) AS INTEGER
  )
Tomalak
getting an error for this
Digital Craft Studios
@Digital Craft Studios: And that would be… which error, exactly?
Tomalak
Line: 130 Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CAST( RIGHT(`class`, LENGTH(`class`) - INSTR(`class`, '-')) AS INTEGER' at line 4
Digital Craft Studios
@Digital Craft Studios: It seems you have not noticed that I had changed my answer a bit. Please try again.
Tomalak
unfortunately, I'm unable to hug you! works like charm thanks... could you refer me to any resource that could teach how to solve queries like these? Thanks a milliom
Digital Craft Studios