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.

tammy_gutter@yahoo.com wrote:
> 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.

Not easily. TAke out the fact that you wish to sort TA2.9 before TB2.8
and it would be easy.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

DA Morgan schrieb:
> tammy_gutter@yahoo.com wrote:
>
>> 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.
>
>
> Not easily. TAke out the fact that you wish to sort TA2.9 before TB2.8
> and it would be easy.

In *this* alphanumeric sequence it would be too ;-)

CREATE TABLE a(b VARCHAR2(10));
insert into a values('.2');
insert into a values('.4');
insert into a values('2');
insert into a values('2.05');
insert into a values('2.22');
insert into a values('2.4');
insert into a values('2.6');
insert into a values('TA2.9');
insert into a values('TB2.8');
insert into a values('3.2');
insert into a values('4.8');
insert into a values('7.1');

SELECT b FROM a
ORDER BY
to_number(translate(decode(b,'TB2.8','TA2.9','TA2. 9','TB2.8',b),'0123456789.ABCDEFGHIJKLMNOPQRSTUVWX YZ','0123456789,'));


Best regards

Maxim

<tammy_gutter@yahoo.com> wrote in message
news:1117673407.535254.94100@g49g2000cwa.googlegro ups.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

Mark C. Stock wrote:
> <tammy_gutter@yahoo.com> wrote in message
> news:1117673407.535254.94100@g49g2000cwa.googlegro ups.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@x.washington.edu
(replace x with u to respond)

Maxim Demenko wrote:
> DA Morgan schrieb:
>
>> tammy_gutter@yahoo.com wrote:
>>
>>> 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.
>>
>>
>>
>> Not easily. TAke out the fact that you wish to sort TA2.9 before TB2.8
>> and it would be easy.
>
>
> In *this* alphanumeric sequence it would be too ;-)
>
> CREATE TABLE a(b VARCHAR2(10));
> insert into a values('.2');
> insert into a values('.4');
> insert into a values('2');
> insert into a values('2.05');
> insert into a values('2.22');
> insert into a values('2.4');
> insert into a values('2.6');
> insert into a values('TA2.9');
> insert into a values('TB2.8');
> insert into a values('3.2');
> insert into a values('4.8');
> insert into a values('7.1');
>
> SELECT b FROM a
> ORDER BY
> to_number(translate(decode(b,'TB2.8','TA2.9','TA2. 9','TB2.8',b),'0123456789.ABCDEFGHIJKLMNOPQRSTUVWX YZ','0123456789,'));
>
>
>
> Best regards
>
> Maxim

You are assuming that you know all of the possible values.
I was assuming that I didn't: That the sort algorithm must
be generic.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

DA Morgan <damorgan@psoug.org> wrote:


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


I strongly suspect that putting 2.9 before 2.8 was a typo on the part
of the OP.


Paul...

--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.2.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, As a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

"DA Morgan" <damorgan@psoug.org> wrote in message
news:1117720426.764832@yasure...
> Mark C. Stock wrote:
>> <tammy_gutter@yahoo.com> wrote in message
>> news:1117673407.535254.94100@g49g2000cwa.googlegro ups.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@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

DA Morgan schrieb:
> Maxim Demenko wrote:
> > DA Morgan schrieb:
> >
> >> tammy_gutter@yahoo.com wrote:
> >>
> >>> 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.
> >>
> >>
> >>
> >> Not easily. TAke out the fact that you wish to sort TA2.9 before TB2.8
> >> and it would be easy.
> >
> >
> > In *this* alphanumeric sequence it would be too ;-)
> >
> > CREATE TABLE a(b VARCHAR2(10));
> > insert into a values('.2');
> > insert into a values('.4');
> > insert into a values('2');
> > insert into a values('2.05');
> > insert into a values('2.22');
> > insert into a values('2.4');
> > insert into a values('2.6');
> > insert into a values('TA2.9');
> > insert into a values('TB2.8');
> > insert into a values('3.2');
> > insert into a values('4.8');
> > insert into a values('7.1');
> >
> > SELECT b FROM a
> > ORDER BY
> > to_number(translate(decode(b,'TB2.8','TA2.9','TA2. 9','TB2.8',b),'0123456789.ABCDEFGHIJKLMNOPQRSTUVWX YZ','0123456789,'));
> >
> >
> >
> > Best regards
> >
> > Maxim
>
> You are assuming that you know all of the possible values.
> I was assuming that I didn't: That the sort algorithm must
> be generic.
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan@x.washington.edu
> (replace x with u to respond)

You are completely right, i was just kidding, sorry.
Although , a little bit sense was in my posting too - i meant:
if you have a sequence with *predictable* amount of *predictable*
exceptions, you can control those exceptions easily with decode.

Best regards

Maxim

Paul wrote:
>
> DA Morgan <damorgan@psoug.org> wrote:
>
>
>
>>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.
>
>
>
> I strongly suspect that putting 2.9 before 2.8 was a typo on the part
> of the OP.
>
>
> Paul...

If you are correct then:

ORDER BY TO_NUMBER(TRANSLATE(column_name, '1ABT', '1')) solves the
problem.

My guess is that TA must come before TB so that TB9.9 would
preceed TA1.0. Lets see if the OP will clarify the matter.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)