Extracting substring from BLOB converted in VARCHAR2

Question

I have this flexible search in Oracle:

select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(p_targetCarts, 4000,1)) 
from {OrderProposal}

and the result is this:

�srjava.util.ArrayListx��a�Isizexpwt 0000031072x

�srjava.util.ArrayListx��a�Isizexpwt 0000031087x

�srjava.util.ArrayListx��a�Isizexpwt 0000032360x

�srjava.util.ArrayListx��a�Isizexpwt 0000040099x

�srjava.util.ArrayListx��a�Isizexpwt 0000040100x

I would now extract the code that is in there. The code is the number that begin with '00000' and end before the 'x' character.

There is a possible way to do this?

Answer 1

Will this work?

select REGEXP_REPLACE(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(p_targetCarts, 4000,1)),'[[:alpha:]]')
  from {OrderProposal}

Answer 2

I found the solution with:

select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(p_targetCarts, 11,60)) 
from {OrderProposal}

In this way I trim the code string to my selection. I hope this helps you.