if the field is always going to have numbers only, you can get a numeric
sort by using an lpad. assuming the field is ten characters long:
order by lpad(numfield,10)
if the field is a mixture of character and number, try a decode in the
order by decode(ltrim(numfield,'1234567890.-
if the field is numeric, the ltrim will return a null string, and the
decode will evaluate to the lpad, otherwise it will return the field
the weakness here is that it will try to sort a string like "123..32-4--6"
as a number. we can avoid that by using a series of nested ltrims to be
more selective about the format:
ltrim(numfield,'- ') -- strips off the leading minus sign and any spaces
ltrim(ltrim(numfield,'- '),'1234567890') -- numbers before a decimal
ltrim(ltrim(ltrim(numfield,'- '),'1234567890'),'.') -- the decimal
ltrim(ltrim(ltrim(ltrim(numfield,'- '),'1234567890'),'.'),'1234567890') --
numbers after the decimal
'),'1234567890'),'.'),'1234567890')) -- trailing spaces
a little cumbersome to type, but you can throw it into a database function
called "mix_sort" or something along those lines and sort by the function.
"uncle tzad's house o' SQL"
ps. anyone notice that there's an improperly formed numeric string that
will fall through the ltrims as numeric? what is it and what's the quick
> Is there a way to sort character fields numerically.
> The character fields I am sorting sometimes contain numbers and
> sometimes contain characters. If I use the function
> to_number(field_name) and a character is in the field I get an
> invalid number error and the whole query fails.
> Steve Raftery
> 19950 Mariner Avenue
> Torrance, CA 90503