get next value of characters field

[nouf]

hi,
i have the following table:

b_no
-------
9
10
11_A
11_B
12
13_A

the b_no is varchar2(7),
i want to get the next item (wich is alaways the number only)
in this example i want to get 14.

regards,,,

 

[sem]

select max(to_number(substr(b_no,1, length(b_no)- length(ltrim(b_no,'0123456789')))))+1 from <table>

But you'd better redesign your table, say split b_no field by number and modifier.

Regards, Dima

 

[nouf]

to sem,,,,
thank you very much, it is working..