PL/SQL extract numbers between characters

user_1710385 0
Viewed 113

I have a string in the format 12345Q999W12345. Basically, some digits followed by 'Q' followed by more digits, followed by 'W' and ends in more digits. I want to extract the number between the characters 'Q' and 'W'. The best that I have been able to come up with is:

select regexp_substr( '12345Q999W12345' , 'Q[^(\d+)$]+W' ) from dual;

The output that I get from the above is:

Q999W

Any pointers on how to further refine this regexp?

oracle sql regex
2 Answers

Figured it out.

select regexp_substr( '12345Q999W12345' , '\Q(\d+)\W', 1, 1, NULL, 1 ) from dual;
user_17103850

I'm not sure what you figured out because your regular expression (posted as an answer) doesn't return anything in my 19c Oracle database.

In the following query,

  • result - my suggestion (forget about regular expression; this is a simple task which is easily solved with good, old substr + instr combination)
  • your_1 - result of your 1st query (posted in a question)
  • your_2 - result of your 2nd query (posted as an answer)
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> with test (col) as
  2    (select '12345Q999W12345' from dual)
  3  select substr(col,
  4                instr(col, 'Q') + 1,
  5                instr(col, 'W') - instr(col, 'Q') - 1
  6               ) result,
  7               --
  8         regexp_substr(col, 'Q[^(\d+)$]+W') your_1,
  9         regexp_substr(col, '\Q(\d+)\W', 1, 1, NULL, 1) your_2
 10  from test;

RESULT     YOUR_1     YOUR_2
---------- ---------- ----------
999        Q999W

SQL>
user_90979060
Related