CODEWITHSUNDEEP
mysql
marek
marek

Reputation: 11

How order mysql data first letters after numbers

I have table with data like below, I tried order by many options but always somethink is wrong. My sql result:

select type from types
order by type ASN;

TYPE 1
TYPE 10
TYPE 11
TYPE 12
TYPE 13
TYPE 14
TYPE 15
TYPE 16
TYPE 17
TYPE 18
TYPE 19
TYPE 2
TYPE 20
TYPE 21

How you see "TYPE 2" is under type 19, I would like get result like below

TYPE 1
TYPE 2
TYPE 10
TYPE 11
TYPE 12
TYPE 13
TYPE 14
TYPE 15
TYPE 16
TYPE 17
TYPE 18
TYPE 19
TYPE 20
TYPE 21

My data in table have diffrent type not always 4 letters sometimes have 3 letters but always is one space between letters and number

Upvotes: 1

Views: 285

Answers (3)

RToyo
RToyo

Reputation: 2877

If you only have data that is one word followed by a number, you can use substring_index to split your string at the space. Then you can order by those two different values. Note that you need to convert the number to an int so that MySQL sorts it numerically, rather than alphanumerically. 

select 
  *
from types
order by
  substring_index(type, " ", 1),
  convert(substring_index(type, " ", -1), signed int)

Edit: I should point out that the reason I didn't suggest simply doing the typical order by length work around, is because the OP said the length of the type column values would be variable (3 or 4 chars).

This is the method that would normally be suggested if you had the exact same prefix before your numbers (eg if it was always "type ###"):

order by char_length(type), type

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

According to sample data below will be work by using substring and POSITION

 select * from types 
 order by CONVERT( substring(type, POSITION(' ' IN type)),UNSIGNED INTEGER) asc

another way by using SUBSTRING_INDEX

select * from types 
order by CONVERT(SUBSTRING_INDEX(type,' ',-1),UNSIGNED INTEGER) asc

http://www.sqlfiddle.com/#!9/314d43/1

http://www.sqlfiddle.com/#!9/314d43/3

Upvotes: 0

Eric Yang
Eric Yang

Reputation: 2750

The underlying column is a string and you're doing a string sort, you need to cast to a number.

CONVERT(type, UNSIGNED INTEGER)

Would convert it to a number.

Upvotes: 0

Related Questions