Order by - numeric order
Hint Ref: 020910220003
Hint Date: 22/10/2009
Hint Details:
Ordering a query result on the basis of a value in a text field will of cause result in a results set ordered alphabetically on the contents of that field in the results. If this text field actually contains numeric characters, then the results set will still be ordered alphabetically because the field is not being treated as a Numeric data type. Thus the order will appear as 1,10,11,12,2,20,21,22...for example.
However, if you wish to treat this text field as a Numeric for the purpose of ordering, so that the resultant order is 1,2,3,4,5,6,7,8,9,10,11..., you will need to convert this text field into an equivalent field (as far as possible because the text field might contain characters that are not numeric) of type Numeric. The function VAL is available to accomplish this and this function will discard any non numeric characters and then convert the result into a numeric.
The following are examples of VAL in use:
VAL('123') = 123
VAL('HYJHH&-JGGTR4FG56G') = 456
Please note that VAL does not return decimals, please use VALDEC() for this.
Thus the resultant Order by statement would need to contain; VAL(<text field>)
Example:
select ourref,* from iqacdocument order by val(ourref)
(Please Note: This Procedure can be destructive and should only be used by Advanced Users. RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure or SQL command. Responsibility resides solely with the user.
IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)