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]

Re: Ordering varchar column numerically

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]

Re: Ordering varchar column numerically

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]

Re: Ordering varchar column numerically


"Simon Hayes" <sql@hayes.ch > wrote in message
news:1107442937 .220047.113030@ o13g2000cwo.goo glegroups.com.. .[color=blue]
> 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[/color]

Thank you Simon, the above syntax worked like a charm!

Frank
[color=blue]
> 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
>[/color]

[blue]

Re: Ordering varchar column numerically


"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:1107443475 .536452.235950@ f14g2000cwb.goo glegroups.com.. .[color=blue]
> 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
> --
>[/color]

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