• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 640
  • Last Modified:

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
0
Ray Paseur
Asked:
Ray Paseur
  • 3
  • 3
  • 2
  • +2
4 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
ORDER BY  cast( columnName as int )  
0
 
Kelvin SparksCommented:
>>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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
zadeveloperCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
try this

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

0
 
zadeveloperCommented:
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
 
zadeveloperCommented:
My script will place numbers first then charaters, Im sure you will figure out how to change that if you want ;)
0
 
Ray PaseurAuthor Commented:
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
 
Ray PaseurAuthor Commented:
Thanks, colleagues!
0
 
Kent OlsenData Warehouse Architect / DBACommented:

>> 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
 
Ray PaseurAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now