Find a string in a string

Navigation
0

I have this String in oracle: 'Linked Order with Sale: 4575987/10' and would like to return only the order number in this case 4575987/10. So far the maximum I've achieved is by passing a fixed value, but you are going to invoke a fixed number I would like to pass THE TEXT FRACTION ('Linked Sales Order:')

follows query: 

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

That way it works, but there are cases where previous information is put in the same string and therefore may not bring the order number. Here are some examples that can 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 that I have fixed are inside an obs field of a table, so they may vary, but the text 'Request Bound with Sale:' can be found inside them if I could from that text search the next 8 characters would be great!

    
asked by anonymous 01.11.2017 / 13:11

1 answer

0

Well, I was able to solve the problem here, doing as below I will always have the value of the request (in this case if the request contains the same size of numbers), or I can hide the value of the substr then it will bring me the complete 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 find from the last: with the argument -1 of the

    
01.11.2017 / 13:38
MySQL while switching from PDO foreach ResultSet is not positioned correctly, you may need to call next