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)
Received on Thu Jun 02 2005 - 08:55:54 CDT
