Page 1 of 1

problem in ORDER BY

Posted: Wed Mar 25, 2009 1:40 am
by anaspk
hi i m Anas Raza..
i want to sort a record by a field let supose the field is registration number which is combination of alphabets and intergers,which is like this
b13
b14
b15
b11
b21
b1111
b1121
b123
b131
when i use ORDER BY thn they look like following:
b1111
b1121
b12
b123
b13
b131
b14
b15
b21
but i dont want sorting like this,i want to sort it in human way i mean like this
b11 (Which "ORDER BY" already ignor)
b12
b13
b14
b15
b21
b123
b131
b1111
b1121
plz help me to do that varchar field in order

Re: problem in ORDER BY

Posted: Wed Mar 25, 2009 10:21 am
by Bill H
Well, by "human way" what you mean is a numeric sort. But the field is alphabetic. Humans can see digits and interpret them as both numbers and letters, but computers cannot do that. Numbers are stored as ones and zeroes only, and the digits that we can see as numbers it sees only as, guess what, alphabetic characters. As far as your database is concerned, there are no numbers here.

The only way to do what you want is to separate the alpha character from the numeric ones, right justify the numeric portion and pad it with spaces.

Re: problem in ORDER BY

Posted: Wed Mar 25, 2009 12:40 pm
by pickle
Please look through the forums & make sure you post in the correct one.

Moving to Databases.