Re: Sorting numbers in a text field

On Jan 17, 4:21 pm, spudtheimpa..._at_gmail.com wrote:
> I have found my own answer, thank you.
>
> Please see code below (note that the +2 on the default in the decode is
> because there is only ever a hyphen and letter, 2 characters. if you
> had a variable no of characters afterwards this will not work)
>
> Thank you for any who looked over it
>
> Regards,
> Mitch.
>
> COLUMN Sorted format a20
>
> SELECT DECODE( INSTR(test,'-'), --test for hyphen
> 0, LPAD( test, --if no hyphen, add padding
> to right align text/numbers
> (SELECT MAX( LENGTH(test) ) FROM
> table),
> ' '
> ), --now use default (if hyphen
> found ie INSTR test !=0
> LPAD( test,
> ( SELECT MAX( LENGTH(test) ) FROM
> table)+2,
> ' '
> )
> )
> AS Sorted
> FROM table
> ORDER BY Sorted;

A database is not a garbage bin.
If your data is numeric: use a number
If your data is string: use a varchar2
Do NOT concatenate attributes in one column. Your application will be a complete mess and it will not perform. So I don't think this is the correct answer. The correct answer is to the run a CREATE DATABASE script, start all over again, and learn to do things *properly*.

-- 
Sybrand Bakker
Senior Oracle DBA