Extracting text between a character and space

I have this text and I want to extract the id of collector that is bolded

knowing that the id is not fixed it May 5 digits or even three digits

EX:

'Date:20190303 Collector:**4840** U:98611 COLLECTION A 647659 N'

what is the best approach to do that by oracle functions

Answer

I think you need REGEXP_SUBSTR as follows:

SQL> with YOUR_DATA as
  2  (select 'Date:20190303 Collector:4840 U:98611 COLLECTION A 647659 N' as str
  3     from dual)
  4  -- Your query starts
  5  SELECT REGEXP_SUBSTR(STR, 'Collector:([^ ]+)',1,1,NULL,1)
  6  FROM YOUR_DATA;

REGE
----
4840

SQL>