Ordering varchar column numerically

[blue]

Is there a preferred way to order a varchar column numerically (for those
that are numeric), then alphanumericall y for all others?

I've tried:

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

and

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

Neither of these however give the desired results.

Any ideas?

Thanks,
Frank

[Simon Hayes]

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(MyCol umn), col1

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

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

Simon

[David Portas]

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
--

[blue]

"Simon Hayes" <sq*@hayes.ch > wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.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(MyCol umn), col1

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

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

Simon

[blue]

"David Portas" <RE************ *************** *@acm.org> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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