order by 1

[nouf]

hi,
i have the following table
b_no
-----
9
11
10_A
13
14
15_A
16

b_no is a varchar2(7) field,,i want
1- to order this field to get
b_no
------
9
10_A
11
13
14
15_A
16

2- to get the next sequence of the b_no ,her i want 17.

 

[SantaMufasa]

Nouf,

1) select b_no from tab order by to_number(translate(b_no,'x_A','x'));

2) select max(to_number(translate(b_no,'x_A','x')))+1 from tab;

Let me know if this is what you wanted or if you have questions about the above code.

Dave
Sandy, Utah, USA @ 05:40 GMT, 22:41 Mountain Time

 

[nouf]

To SantaMufasa

,,,
first of all in my field i may have 15_B also not only A is used,,

also when i use [ order by to_number(.....) ] in developer
it gives me an error about it.

i used your code but ther was an error which is :
ORA-01722: invalid number (about translate)

so can you help please;

 

[nouf]

THE ERROR ORA-01722: invalid number APPEARS WHEN I USE
TO_NUMBER NOT ABOUT TRANSLATE....
ALSO I MAY USE ALL CHARACTERS IN (_X)

 

[sem]

Nouf, haven't it be answered in thread186-692778 ? Just think a little bit. Do you find it to be polite to ask how to extract number part of that field after obtaining an answer about how to extract it and add 1 to it?

Regards, Dima

 

[SantaMufasa]

Nouf,

Since your post did not mention other non-numeric characters besides "A", I didn't solve for characters for which I was not aware. Of course, you should add to the TRANSLATE character list any characters that are non-numeric so that you do not receive the "ORA-01722: invalid number" error. I have modified both statements to handle all characters of which I am aware: "_" plus all alphabetic characters, both upper and lower case:

1) select b_no from tab order by to_number(translate(upper(b_no),'^_ABCDEFGHIJKLMNOPQRSTUVWXYZ','^'));

2) select max(to_number(translate(upper(b_no),'^_ABCDEFGHIJKLMNOPQRSTUVWXYZ','^')))+1 from tab;

Let me know if this solves your need.

Dave
Sandy, Utah, USA @ 18:19 GMT, 11:19 Mountain Time

 

[nouf]

to santamufasa:

thank you very much it is working well now....