Find text in sequence in a string

Question

I have this String in oracle: 'Order linked with Sale : 4575987/10' and I would like to return only the order number in this case the 4575987/10. Until now the maximum I could is passing a fixed value, but instead of a fixed number I would like to pass THE FRACTION OF THE TEXT ('Order linked to the Sale :')

pursuance query:

SELECT TO_CHAR(SUBSTR(LTRIM('Pedido vinculado com a Venda : 57994/73'), 32)) FROM dual;

This way it works, but there are cases where a previous information is put in that same string and so may not bring the order number. Following are examples that may occur:

  SELECT TO_CHAR(SUBSTR(LTRIM('Volume: 235 rolos Pedido vinculado com a Venda : 53160/73'), 32)) FROM dual;

  SELECT TO_CHAR(SUBSTR(LTRIM('ENVIAR SOMENTE ROLOS DE FABRICAÇÃO RECENTE E PRIORITARIAMENTE ROLOS COM 55 M2. Pedido vinculado com a Venda : 55814/73'), 32)) FROM dual;

these examples I have passed fixed are within a table’s Obs field, and so may vary, but the text 'Order linked to Sale :' can be found inside them, if I could from that text find the next 8 characters would be great!

Answer 1

Well, I managed to solve the problem here, doing as below I will always have the order value(in this case if the request contains the same number), or I can hide the value of the substr then it will bring me the full number:

SELECT LTRIM(REPLACE(SUBSTR('ENVIAR SOMENTE ROLOS DE FABRICAÇÃO RECENTE E PRIORITARIAMENTE ROLOS COM 55 M2.
Pedido vinculado com a Venda : 55814/73',  INSTR('ENVIAR SOMENTE ROLOS DE 
FABRICAÇÃO RECENTE E PRIORITARIAMENTE ROLOS COM 55 M2.
Pedido vinculado com a Venda : 55814/73', ': ', -1, 1), 10), ':')) FROM DUAL;

So I can pass any text that it will always locate from the last : with the argument -1 of the instr.