"DA Morgan" <damorgan_at_psoug.org> wrote in message
news:1117720426.764832_at_yasure...
> Mark C. Stock wrote:
>> <tammy_gutter_at_yahoo.com> wrote in message
>> news:1117673407.535254.94100_at_g49g2000cwa.googlegroups.com...
>>
>>>Hi,
>>>
>>>Is there a way to sort an alphanumeric column in this order?
>>>Basically, I want to ignore the alpha characters:
>>>
>>>.2
>>>.4
>>>2
>>>2.05
>>>2.22
>>>2.4
>>>2.6
>>>TA2.9
>>>TB2.8
>>>3.2
>>>4.8
>>>7.1
>>>
>>>Thanks in advance.
>>>
>>
>>
>> sort on an expression that converts the column value to a a numeric
>>
>> be sure to use an expression that is not specific to the current data
>> values
>>
>> one possible expression involves nesting the TRANSLATE function, using it
>> once to create a mask of unwanted characters that you want to strip out,
>> and a second time to strip out the unwanted characters. then convert the
>> resulting string to a number. (picked up the double TRANSLATE trick from
>> an earlier post in these forums)
>>
>> take a look at the B_NUM expression here:
>>
>> SQL> select *
>> 2 from
>> 3 (
>> 4 select
>> 5 b
>> 6 , translate(b,'x1234567890.', 'x') b_nonnum
>> 7 , to_number( translate( b,'1' || translate(b,'x1234567890.', 'x'),
>> '1')) as b_num
>> 8 from a
>> 9 )
>> 10 order by b_num
>> 11 /
>>
>> B B_NONNUM B_NUM
>> ---------- ---------- ----------
>> .2 .2
>> .4 .4
>> 2 2
>> 2.05 2.05
>> 2.22 2.22
>> 2.4 2.4
>> 2.6 2.6
>> TB2.8 TB 2.8
>> TA2.9 TA 2.9
>> 3.2 3.2
>> 4.8 4.8
>> 7.1 7.1
>>
>> 12 rows selected.
>>
>> ++ mcs
>
> You got 2.8 before 2.9 which was my stopping point. It is easy if
> that is the correct answer but the OP indicated it was not.
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan_at_x.washington.edu
> (replace x with u to respond)
2.9 before 2.8 is likely a typo, but i should have confirmed that
tammy, what's the case? are you really ignoring all aphas or does the TA2.9
really need to come before the TA2.8?
++ mcs
Received on Thu Jun 02 2005 - 09:48:11 CDT
