Re: Sorting alphanumeric column

<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 Received on Thu Jun 02 2005 - 06:51:10 CDT