hi i m Anas Raza.. i want to sort a record by a field let supose the field is registration number which is combination of alphabets and intergers,which is like this b13 b14 b15 b11 b21 b1111 b1121 b123 b131 when i use ORDER BY thn they look like following: b1111 b1121 b12 b123 b13 b131 b14 b15 b21 but i dont want sorting like this,i want to sort it in human way i mean like this b11 (Which "ORDER BY" already ignor) b12 b13 b14 b15 b21 b123 b131 b1111 b1121 plz help me to do that varchar field in order
If it will always be the letter "b" followed by digits, then you can do: SELECT ... ORDER BY CAST(SUBSTR(registration_number, 2) AS UNSIGNED INTEGER) Code (markup): Advanced tip: If you will be doing frequent queries with large result sets, you should create another column to hold the integer equivalents and optionally put a key on it. Otherwise this will become slow under load.