mySQL sorting ASCII as INT [duplicate]

Question

In a SQL table there is a client_num column declared as char(5). When I sort the table by client_num with ASC it will have the typical ASCII sort.

How to sort client_num as an INT while keeping the column still a type of char(5)?

EDIT

The above mentioned SQL order string as number has quite extensively answers. This question is simple and has straight answers. Though it is a duplicate. But it helps me very quickly.

try .. ORDER BY client_num+0 or .. ORDER BY CAST(client_num AS UNSIGNED) — 1000111
– 1000111, Commented Sep 18, 2016 at 7:12
MySQL converts a string to numeric type when added zero like above. You could try the second option too. — 1000111
– 1000111, Commented Sep 18, 2016 at 7:18
Ok - I was to fast. It`s for casting AND it works perfect !!! — Ben
– Ben, Commented Sep 18, 2016 at 7:18

Mureinik · Accepted Answer · 2016-09-18 07:15:54Z

1

You could cast the column to an integer:

SELECT   *
FROM     mytable
ORDER BY CAST(client_num AS INT)

answered Sep 18, 2016 at 7:15

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

5 Comments

Ben Over a year ago

Casting is the solution, yes. What is the difference here between UNSIGNED and INT ?

Ed Heal Over a year ago

INT can be -ve, unsigned cannot

Mureinik Over a year ago

@Ben unsigned has only non-negative numbers, while ints can be negative.

Ben Over a year ago

well - stupid that question. So mySQl is finally doing the same cast.

Ben Over a year ago

To whom it may concern : CAST(client_num as INT) brings up a syntax error whlie CAST(client_num as UNSIGEND) not. Thats maybe a question of mySQL versions, don't know.

Collectives™ on Stack Overflow

mySQL sorting ASCII as INT [duplicate]

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related