Finding the index of last occurrence

Database

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=abc
abc_ppc_abdd_hello_1234P;name=abc
abc_ppc_abdd_here_it_is_1234P;name=abc
abc_ppc_abdd_tesing_1234P;name=abc

I need the position of '_' before 1234P;

Your help will be greatly appreciated.

Thanks
Vis

2 REPLIES
Enthusiast

Re: Finding the index of last occurrence

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');

Select
PK, SrString,
Instr(SrString,'_',-1,1)
From T1
Order by 1
;

Returns:
PK SrString Instr(SrString,'_',-1,1)
1 abc_ppc_abdd_1234P 13
2 abc_ppc_abdd_hello_1234P 19
3 abc_ppc_abdd_here_it_is_1234P 24
4 abc_ppc_abdd_tesing_1234P 20

Enthusiast

Re: Finding the index of last occurrence

Hey,

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