Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: sorting character fields

Re: sorting character fields

From: tzadkiel <tzadkiel_at_surfnetcity.com.au>
Date: 1998/03/11
Message-ID: <01bd4d32$1b99a6c0$76ce6ccb@default>#1/1

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:

select numfield
from tablex
order by lpad(numfield,10)

if the field is a mixture of character and number, try a decode in the following format:

select numfield
from tablex
order by decode(ltrim(numfield,'1234567890.- '),'',lpad(numfield,10),numfield)

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

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

ltrim(ltrim(ltrim(ltrim(ltrim(numfield,'- '),'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.

tzad

"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 solution?         

Steve Raftery <sraft_at_webvision.com> wrote in article <3506CFAD.167E_at_webvision.com>...
> 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
>
> Webvision
> 19950 Mariner Avenue
> Torrance, CA 90503
> www.webvision.com
>
Received on Wed Mar 11 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US