Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ORDER BY with Numeric Character Strings

Posted on 2010-01-07
11
Medium Priority
?
638 Views
Last Modified: 2013-12-12
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
0
Comment
Question by:Ray Paseur
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26205007
ORDER BY  cast( columnName as int )  
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 26205037
>>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
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 26205053
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
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 13

Accepted Solution

by:
zadeveloper earned 1500 total points
ID: 26205176
This script should help you

declare @tempTable table (MyValues varchar(10))


insert into @tempTable values('10')
insert into @tempTable values('9')
insert into @tempTable values('p')
insert into @tempTable values('a')
insert into @tempTable values('113')
insert into @tempTable values('999')
insert into @tempTable values('189')

select 
	* ,
	case isnumeric(MyValues) when 1 then cast(MyValues as numeric) else 999999 end
from 
	@temptable 
order by 
	case isnumeric(MyValues) when 1 then cast(MyValues as numeric) else 999999 end,
	case isnumeric(MyValues) when 1 then '' else MyValues end

Open in new window

0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 500 total points
ID: 26205188
try this

order by CASE WHEN ISNUMERIC(ColumnName) = 1 THEN CAST (columnName as int)  ELSE ASCII(ColumnName) end

0
 
LVL 13

Assisted Solution

by:zadeveloper
zadeveloper earned 1500 total points
ID: 26205191
sorry : left some stuff in the select : use this select

select 
	* 
from 
	@temptable 
order by 
	case isnumeric(MyValues) when 1 then cast(MyValues as numeric) else 999999 end,
	case isnumeric(MyValues) when 1 then '' else MyValues end

Open in new window

0
 
LVL 13

Assisted Solution

by:zadeveloper
zadeveloper earned 1500 total points
ID: 26205208
My script will place numbers first then charaters, Im sure you will figure out how to change that if you want ;)
0
 
LVL 111

Author Comment

by:Ray Paseur
ID: 26206271
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

0
 
LVL 111

Author Closing Comment

by:Ray Paseur
ID: 31674290
Thanks, colleagues!
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 26209195

>> 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
0
 
LVL 111

Author Comment

by:Ray Paseur
ID: 26278115
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
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month20 days, 16 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question