41

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

sreenavc
  • 729
  • 3
  • 10
  • 19
  • 5
    Why is it not a number in the first place? – Ignacio Vazquez-Abrams Mar 24 '11 at 09:58
  • If you have lots of records and you want to convert in each query, it is going to be a nightmare. Please review your database design again, use an appropriate column type. – Sarwar Erfan Mar 24 '11 at 10:03
  • Wordpress stores custom fields as text. If you have a custom field such as a lat/lon, it's slow to convert to do lookups if your table is large. Storing as zero padded numbers in your text field is the only way to avoid the conversion and allow proper sort. Caution! Negative numbers stored as text sort the OPPOSITE direction as real numbers! – Neal Bozeman Apr 01 '20 at 23:25

6 Answers6

148

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
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • 1
    Simpliest? - yes. Quickest? - not sure, because conversion occurs. – Devart Mar 24 '11 at 11:54
  • 2
    @Devart - edited. It will be faster than any explicit conversion. Quick (1) to write - definitely (2) to execute - quite possibly (3) bonus extras.. priceless – RichardTheKiwi Mar 24 '11 at 11:57
  • Yes, 'underflow' in this case is important. – Devart Mar 24 '11 at 12:43
  • like this solution, especially the 3rd bonus. Another tip is to use abs() function, but that only works if the column doesn't have negative numbers. And that doesn't support the 3rd feature. Though if you want the best performance, adding a numeric type column is the way to go. Again, that doesn't support 3rd bonus feature. – wanghq Jan 08 '12 at 00:51
  • @RichardTheKiwi how does this actually work? if possible can you share a link for this feature documentation.. thanks :) – Abhishek Nov 28 '18 at 08:59
21

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.

denny
  • 2,084
  • 2
  • 15
  • 19
MarzSocks
  • 4,229
  • 3
  • 22
  • 35
13

Use a CAST or a CONVERT function.

Devart
  • 119,203
  • 23
  • 166
  • 186
  • But I agree with other posters - it can be slow on big tables. – Devart Mar 24 '11 at 10:22
  • i tried with this data 1,2.5,2,2.6. Given the query as SELECT column FROM table ORDER BY CAST(column AS unsigned).But it sorts as 1,2,2.6,2.5 – sreenavc Mar 24 '11 at 10:47
  • CAST(column AS DECIMAL), as WhiteFang34 suggested – Devart Mar 24 '11 at 11:26
  • CREATE TABLE table_test(column1 VARCHAR(255)); INSERT INTO table_test VALUES ('1'),('2.5'),('2'),('2.6'); SELECT column1 FROM table_test ORDER BY CAST(column1 AS DECIMAL); 1, 2, 2.5, 2.6 – Devart Mar 24 '11 at 11:47
  • Pls try with this. INSERT INTO `table_test` (`column1`) VALUES ('1'), ('2.5'), ('2'), ('2.6'), ('1'), ('2.5'), ('2'), ('2.6'); result shows as wrong.shown as 1,1,2,2,2.5,2.6,2.5,2.6 – sreenavc Mar 24 '11 at 11:59
  • 3
    See manual - DECIMAL data type, DECIMAL[(M[,D])]. Try something like this - DECIMAL(10, 5). – Devart Mar 24 '11 at 12:35
  • what happens when the column contains text as well? e.g. "unit-1", "unit-2"..."unit-15" ? – ierdna Mar 30 '16 at 00:35
8

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/.

Zon
  • 18,610
  • 7
  • 91
  • 99
7

Pad the string with leading zeroes:

ORDER BY LPAD(`column`,<max length of string>,"0")
Marius95
  • 71
  • 1
  • 1
  • THIS is the best solution. I had a column with the numbers 1-60 and row for >60 (which is the text). I can't convert the column to a number, but padding the column with a 0 in the order by section worked perfectly. – Jared Mar 10 '16 at 21:42
  • This doesn't work, it puts "1562.10" below "7160.1". This is due to the padding. ie: 007160.1 comes before 01562.10 – Ben Hitchcock Mar 13 '18 at 07:30
4

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.

WhiteFang34
  • 70,765
  • 18
  • 106
  • 111
  • 1
    Upvoters: This is wrong, ok. It would be right if DECIMAL is given a precision, and then only if it is compatible with the source data. – RichardTheKiwi Mar 24 '11 at 12:45
  • @Richard: it works for me using MySQL 5.1.54 without any precision specified for `DECIMAL`. I'll update my answer with exactly the output I got. Give it a try, am I overlooking something? I otherwise don't get the right order unless that `ORDER BY CAST` is specified. – WhiteFang34 Mar 24 '11 at 16:21
  • 1
    WhiteFang - DECIMAL without specifier is 0-dp. Try 1.2,1.4,1.2,1.4,1.2. They all get CAST to 0. You can include the column, `SELECT bar, CAST(bar AS DECIMAL) FROM foo` As it is your test numbers are all significantly different – RichardTheKiwi Mar 24 '11 at 22:03
  • @Richard: thanks, indeed you're right. Good to know, I glossed over that. Updated answer. – WhiteFang34 Mar 24 '11 at 22:25
  • And for that you would have to know the right size for the decimal. There could be 1.123, 1.125 etc - so I just prefer `* 1` – RichardTheKiwi Mar 24 '11 at 22:40