CODEWITHSUNDEEP
mysql
sreenavc
sreenavc

Reputation: 747

mysql sort string number

I have a column of type varchar that stores many different numbers. Say for example there are 3 rows: 17.95, 199.95 and 139.95.How can i sort that field as numbers in mysql

Upvotes: 45

Views: 62792

Answers (6)

MarzSocks
MarzSocks

Reputation: 4318

If you need to sort a char column containing text AND numbers then you can do this.

tbl contains: 2,10,a,c,d,b,4,3

select * from tbl order by number_as_char * 1 asc, number_as_char asc

expected output: 2,3,4,10,a,b,c,d

If you don't add the second order by argument only numbers will be sorted - text actually gets ignored.

Upvotes: 23

Zon
Zon

Reputation: 19880

This approach is helpful when sorting text as numbers:

SELECT `my_field` 
FROM `my_table` 
ORDER BY `my_field` + 0;

Found the solution on http://crodrigues.com/trick-mysql-order-string-as-number/.

Upvotes: 8

Marius95
Marius95

Reputation: 71

Pad the string with leading zeroes:

ORDER BY LPAD(`column`,<max length of string>,"0")

Upvotes: 7

WhiteFang34
WhiteFang34

Reputation: 72039

If you really have to you can do this if your source data is compatible:

SELECT column FROM table ORDER BY CAST(column AS DECIMAL(10,2))

It's not going to be very fast for large data sets though. If you can you should change the schema to use DECIMAL in the first place though. Then it can be properly indexed for better performance.

Upvotes: 4

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

Quickest, simplest? use * 1

select *
from tbl
order by number_as_char * 1

The other reasons for using * 1 are that it can

  1. survive some horrendous mishaps with underflow (reduced decimal precision when choosing what to cast to)
  2. works (and ignores) columns of purely non-numeric data
  3. strips numeric portions of alphanumeric data, such as 123A, 124A, 125A

Upvotes: 154

Devart
Devart

Reputation: 121902

Use a CAST or a CONVERT function.

Upvotes: 13

Related Questions