45

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

Improve this question
3
  • 5
    Why is it not a number in the first place? Commented Mar 24, 2011 at 9: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. Commented Mar 24, 2011 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! Commented Apr 1, 2020 at 23:25

6 Answers 6

Reset to default
157

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
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Simpliest? - yes. Quickest? - not sure, because conversion occurs.
@Devart - edited. It will be faster than any explicit conversion. Quick (1) to write - definitely (2) to execute - quite possibly (3) bonus extras.. priceless
Yes, 'underflow' in this case is important.
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.
@RichardTheKiwi how does this actually work? if possible can you share a link for this feature documentation.. thanks :)
|
24

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.

Improve this answer

1 Comment

This is really a nice small hack. =)
14

Use a CAST or a CONVERT function.

Improve this answer

7 Comments

But I agree with other posters - it can be slow on big tables.
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
CAST(column AS DECIMAL), as WhiteFang34 suggested
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
See manual - DECIMAL data type, DECIMAL[(M[,D])]. Try something like this - DECIMAL(10, 5).
|
9

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

Improve this answer

Comments

7

Pad the string with leading zeroes:

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

2 Comments

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.
This doesn't work, it puts "1562.10" below "7160.1". This is due to the padding. ie: 007160.1 comes before 01562.10
5

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.

Improve this answer

5 Comments

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.
@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.
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
@Richard: thanks, indeed you're right. Good to know, I glossed over that. Updated answer.
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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.