I want to use substring and get values according to my pattern

select substr(e.orgmessage, 40, 50) as one,
      开发者_开发知识库 orgmessage as original_message
  from table

The result in the column one is:

00 02182 00363 00008 O    PKR      500 01983 00319

but the original string is:

09:22:12 CASH COUNTERS(C)    PKR     1000 02182 00363 00008 O    PKR      500 01983 00319 00315 O    ***        0 00000 00000 00000 N    ***        0 00000 00000 00000 N

I am unable to get substring to return what I want. I want values like 500 and 1000. The data type of the field is VARCHAR2(1000 Byte).

Use INSTR to find the position of the substring in the source string, then use substr to parse it out from that position.

If your string follows a regular pattern every time then you should be able to pull your 2 values out easily. The 500 and 1000 in your sample string come after the PKR indicators. If the string is regular then the values begin around character 33 and 69 and run for 9 characters each. Using that we can use:

select substr(txt, 33, 9) value1,
       substr(txt, 69, 9) value2
  from (select '09:22:12 CASH COUNTERS(C)    PKR     1000 02182 00363 00008 O    PKR      500 01983 00319 00315 O    ***        0 00000 00000 00000 N    ***        0 00000 00000 00000 N' txt
          from dual
       )

which returns:

VALUE1    VALUE2
--------- ---------
     1000       500

1 row selected.

If your string is not regular and you need to split the string at each PKR and then pull out the 9 characters after the PKR, you will have to use fancier SQL than just a plain substr.