CODEWITHSUNDEEP
sqloracle-databasesubstring
MiSFhE
MiSFhE

Reputation: 3

Substring after nth occurrence of substring in a string in oracle

I want to substring a string like this from oracle db:

12/10 10:43:32: - 170000 subs exported<br />12/10 10:43:32: - 175000 subs exported<br />12/10 10:43:32: - 180000 subs exported<br />12/10 10:43:32: - 185000 subs exported<br />12/10 10:43:32: - 190000 subs exported<br />12/10 10:43:32: - 195000 subs exported<br />12/10 10:43:32: - 200000 subs exported<br />12/10 10:43:32: - 200400 subs exported<br /> 12 / 10 10 :43 :32 :processing completed successfully

to just last 3 line:

12/10 10:43:32: - 200000 subs exported<br />12/10 10:43:32: - 200400 subs exported<br /> 12 / 10 10 :43 :32 :processing completed successfully

I have try this: substr(string, instr(string, '<br />', 1, 1)+2)

But in some cases I don't know how many <br /> there are, so I find it difficult to found begin index of last 3 line. (Data type of this column is CLOB)

Upvotes: 0

Views: 1225

Answers (2)

user9601310
user9601310

Reputation: 1086

In Oracle, you can also find the 3rd occurrence of 'br />' counting from the end of the string backwards, by specifying a negative value (i.e -1) for the 3rd parameter of INSTR and your desired occurrence number (i.e. 3) as the 4th parameter. e.g.

SELECT SUBSTR(mytext, INSTR(mytext, '<br />', -1, 3) + 6) AS answer FROM test

This should also give you the last 3 lines.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

This might be one option:

  • regexp_count finds number of <br /> strings within the column (so you'll have to subtract 2 from that number to get the last 3 lines
  • the rest is simple; use SUBSTR which starts from the previously calculated position and return the rest of the column value
SQL> select
  2    substr(col, instr(col, '<br />', 1, regexp_count(col, '<br />') - 2) + 6) result
  3  from test;

RESULT
--------------------------------------------------------------------------------
12/10 10:43:32:  - 200000 subs exported<br />12/10 10:43:32:  - 200400 subs expo

SQL>

Upvotes: 1

Related Questions