Home » Applications » Oracle Fusion Apps & E-Business Suite » Doubt on substr
Doubt on substr [message #624224] Fri, 19 September 2014 04:48 Go to next message
na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore
Member

I need to identify dynamically which is last underscore and return after the last underscore values

select '2477882_COLL_1' from field_val

O/P Should be - 1

I have values like this -- 2477882_COLL_MAN_3
2477882_COLL_MAN_3
2477882_COLL_MAN_2
2477882_COLL_MAN_3
Re: Doubt on substr [message #624227 is a reply to message #624224] Fri, 19 September 2014 05:02 Go to previous message
Littlefoot
Messages: 21591
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Find location of the last underscore (INSTR with a negative "position" parameter value), extract the rest of the string (SUBSTR), such as
SQL> with test as
  2    (select '2477882_COLL_1' col from dual)
  3  select substr(col, instr(col, '_', -1) + 1)
  4  from test;

S
-
1
Previous Topic: PO line item quantity after GRN deliver
Next Topic: Using fnd_function.execute to invoke another form in oracle applications
Goto Forum:
  


Current Time: Fri Sep 18 06:20:31 CDT 2020