sorting numbers stored in text field in MYSQL database

Question

i have a mySQL database with a text field in which is stored a number. i need to produce a recordset sorted in descending numerical order. this works fine until we get to numbers greater than 10 ie

is there a simple way of sorting this 'correctly' ? (yes, i know i should have numbers in a numerical field, but i'm working with what i have :))

i'm using the results on an asp/vbscript/jquery page so maybe even a client-side solution is viable...

any suggestions?

Summoner · Accepted Answer · 2012-07-30 10:52:22Z

1

ORDER BY ABS(text_column) DESC

Or, if you also have to deal with negative values:

ORDER BY CAST(text_column AS SIGNED) DESC

answered Jul 30, 2012 at 10:52

Summoner

1,4171 gold badge10 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dog Over a year ago

perfect solution.. thank you... didn't think it'd be that easy :)

Omesh · Accepted Answer · 2012-07-30 10:54:42Z

0

You need to type cast it to INTEGER using CAST function in MySQL:

ORDER BY CAST(text_column AS UNSIGNED INTEGER)

answered Jul 30, 2012 at 10:54

Omesh

29.4k6 gold badges44 silver badges51 bronze badges

2 Comments

juergen d Over a year ago

You are right. It works. It doesn't if you leave the unsigned.

Omesh Over a year ago

ya INTEGER keyword is optional.

Devart · Accepted Answer · 2012-07-30 10:56:58Z

0

Try this one -

... ORDER BY text_column * 1

answered Jul 30, 2012 at 10:56

Devart

120k23 gold badges172 silver badges189 bronze badges

Collectives™ on Stack Overflow

sorting numbers stored in text field in MYSQL database

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related