All Forums Database
greatvissu 3 posts Joined 11/09
16 Nov 2009
Finding the index of last occurrence

Hi experts,please help me out in finding index of last occurrence. for finding first occurrence index( string,'_'). - Returns the index of position of '_' in the given string. Example.abc_ppc_abdd_1234P; name=abcabc_ppc_abdd_hello_1234P; name=abcabc_ppc_abdd_here_it_is_1234P; name=abcabc_ppc_abdd_tesing_1234P;name=abcI need the position of '_' before 1234P;Your help will be greatly appreciated. ThanksVis


Jimm 298 posts Joined 09/07
17 Nov 2009

You need to get the Oracle UDF's installed on your system.The "Instr" function (compatible with Oracle) allows backward searches.The following script sets up a test and gives the correct values:Create Volatile table T1(PK Smallint Not Null, SrString Char(30) Not Null)On Commit Preserve rows;Insert Into T1 Values (1,'abc_ppc_abdd_1234P');Insert Into T1 Values (2,'abc_ppc_abdd_hello_1234P');Insert Into T1 Values (3,'abc_ppc_abdd_here_it_is_1234P');Insert Into T1 Values (4,'abc_ppc_abdd_tesing_1234P');SelectPK, SrString,Instr(SrString,'_',-1,1)From T1Order by 1;Returns:PK SrString Instr(SrString,'_',-1,1)1 abc_ppc_abdd_1234P 132 abc_ppc_abdd_hello_1234P 193 abc_ppc_abdd_here_it_is_1234P 244 abc_ppc_abdd_tesing_1234P 20

Sunar 59 posts Joined 02/08
17 Dec 2009

Hey,You can find out the position of 1234 then -1, gives you the poistion of "_" before 1234.

You must sign in to leave a comment.