By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,175 Members | 1,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,175 IT Pros & Developers. It's quick & easy.

Ordering varchar column numerically

P: n/a
Is there a preferred way to order a varchar column numerically (for those
that are numeric), then alphanumerically for all others?

I've tried:

ORDER BY CASE WHEN IsNumeric(<column_name>) = 1 THEN CONVERT(Float,
<column_name>) ELSE 999999999 END;

and

ORDER BY CASE WHEN IsNumeric(<column_name>) = 1 THEN 0 ELSE 1 END, CASE WHEN
IsNumeric(<column_name>) = 1 THEN CONVERT(Float, <column_name>) ELSE 999999
END, <column_name>;

Neither of these however give the desired results.

Any ideas?

Thanks,
Frank
Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
It would be useful to see some sample data - are your 'numeric' data
integers, decimals, or floats; are there negative numbers in the data,
etc.? Assuming that your data is made up of either positive integers or
strings, then this should work:

select *
from dbo.MyTable
order by case when MyColumn like '%[^0-9]%' then 1 else 0 end, MyColumn

ISNUMERIC() might work, but it considers so many things to be numbers
that you may not get the results you want:

select *
from dbo.MyTable
order by isnumeric(MyColumn), col1

If this doesn't help, then please post DDL and sample data:

http://www.aspfaq.com/etiquette.asp?id=5006

Simon

Jul 23 '05 #2

P: n/a
Assuming your numerics are just positive integers, try:

SELECT col
FROM T1
ORDER BY
CASE WHEN col LIKE '%[^0-9]%'
THEN 9E99
ELSE CAST(col AS INTEGER) END
, col

--
David Portas
SQL Server MVP
--

Jul 23 '05 #3

P: n/a

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
It would be useful to see some sample data - are your 'numeric' data
integers, decimals, or floats; are there negative numbers in the data,
etc.? Assuming that your data is made up of either positive integers or
strings, then this should work:

select *
from dbo.MyTable
order by case when MyColumn like '%[^0-9]%' then 1 else 0 end, MyColumn
Thank you Simon, the above syntax worked like a charm!

Frank
ISNUMERIC() might work, but it considers so many things to be numbers
that you may not get the results you want:

select *
from dbo.MyTable
order by isnumeric(MyColumn), col1

If this doesn't help, then please post DDL and sample data:

http://www.aspfaq.com/etiquette.asp?id=5006

Simon

Jul 23 '05 #4

P: n/a

"David Portas" <RE****************************@acm.org> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Assuming your numerics are just positive integers, try:

SELECT col
FROM T1
ORDER BY
CASE WHEN col LIKE '%[^0-9]%'
THEN 9E99
ELSE CAST(col AS INTEGER) END
, col

--
David Portas
SQL Server MVP
--


Thanks David, this one actually works better. If I use:

order by case when <mycolumn> like '%[^0-9]%' then 1 else 0 end, <mycolumn>

It does not correctly order the integers (i.e. 1,10,11,12,2,3,4,...), but
does correctly order the alphnumerics. Using your suggestion, it works for
both cases.

Frank
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.