ORDER BY with Numeric Character Strings
I have a table containing a column with numeric character string, examples:
998
999
1000
1001
This is a "varchar" column, so when you order it DESC, the result is 999, 998, 1001, 1000
What I really need is to make a selection ordered as if this were an "int" column, so the order would be 1001, 1000, 999, 998.
It is not feasible for me to alter the table because there may be non-numeric values in this column, too.
Is there a way to ORDER BY the natural-number value of the character string?
Thanks and regards to all, ~Ray
998
999
1000
1001
This is a "varchar" column, so when you order it DESC, the result is 999, 998, 1001, 1000
What I really need is to make a selection ordered as if this were an "int" column, so the order would be 1001, 1000, 999, 998.
It is not feasible for me to alter the table because there may be non-numeric values in this column, too.
Is there a way to ORDER BY the natural-number value of the character string?
Thanks and regards to all, ~Ray
Aneesh
ORDER BY cast( columnName as int )
>>It is not feasible for me to alter the table because there may be non-numeric values in this column, too
Due to this you cannot do anything other than the alphanumeric sort - which is what you are getting. The only hope is to add a numeric column with the numeric part of the data items and use that for a sort.
Kelvin
Due to this you cannot do anything other than the alphanumeric sort - which is what you are getting. The only hope is to add a numeric column with the numeric part of the data items and use that for a sort.
Kelvin
If there is a non-numeric value in the row the cast will raise an exception.
You can always prepend a string of zeros and sort on the rightmost characters (digits).
ORDER BY RIGHT ( '00000000' || columnname, 8)
Good Luck,
Kent
You can always prepend a string of zeros and sort on the rightmost characters (digits).
ORDER BY RIGHT ( '00000000' || columnname, 8)
Good Luck,
Kent
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Admittedly, I have not given this a very scientific evaluation, but here are some of the things I found...
This seems to work for the numbers, and it converts the non-numeric values to zero, which happens to be OK for my purposes. Plus it is easy to modify a handful of queries with a simple change in the source code ;-)
Original query that did not work:
SELECT loan_id ... ORDER BY loan_id DESC
Replacement query that orders things right:
SELECT loan_id+0 as loan_id ... ORDER BY loan_id DESC
ORDER BY cast( columnName as int ) ...threw a syntax error
ORDER BY RIGHT ( '00000000' || columnname, 8) ... gave a bizarre, semi-random output series.
I did not test this one, but it looks right, in an intuitive way.
order by CASE WHEN ISNUMERIC(ColumnName) = 1 THEN CAST (columnName as int) ELSE ASCII(ColumnName) end
Thanks to all for your help. Best regards, ~Ray
This seems to work for the numbers, and it converts the non-numeric values to zero, which happens to be OK for my purposes. Plus it is easy to modify a handful of queries with a simple change in the source code ;-)
Original query that did not work:
SELECT loan_id ... ORDER BY loan_id DESC
Replacement query that orders things right:
SELECT loan_id+0 as loan_id ... ORDER BY loan_id DESC
ORDER BY cast( columnName as int ) ...threw a syntax error
ORDER BY RIGHT ( '00000000' || columnname, 8) ... gave a bizarre, semi-random output series.
I did not test this one, but it looks right, in an intuitive way.
order by CASE WHEN ISNUMERIC(ColumnName) = 1 THEN CAST (columnName as int) ELSE ASCII(ColumnName) end
Thanks to all for your help. Best regards, ~Ray
ASKER
Thanks, colleagues!
>> ORDER BY RIGHT ( '00000000' || columnname, 8) ... gave a bizarre, semi-random output series.
The double pipe is an ANSI shortcut for the concat() function. If your MySQL settings don't have ANSI enabled, you'll probably get the random output that you saw.
ORDER BY RIGHT ( concat ('00000000', columnname), 8)
That should work regardless of the ANSI setting.
Kent
ASKER
Kent, thanks for your followup - I will try that. I guessed at the meaning of the concatenation, but since I have no idea whether ANSI is enabled - and it may not be - that could have been the issue that cause the weird ordering. Thanks again, ~Ray