How to extract substring matching particular pattern from text?

with
  sample_data (log_text) as (
    select 'some trashes CID: T17654, rest of trashes'    from dual union all
    select 'some trashes CID: 2876543298 rest of trashes' from dual union all
    select 'some trashes CID:2876543298 rest of trashes'  from dual union all
    select 'some trashes CID: A-876'                      from dual
  )
select log_text, regexp_substr(log_text, 'CID: ?([^ ,]*)', 1, 1, null, 1) as result
from   sample_data
;

LOG_TEXT                                     RESULT         
-------------------------------------------- ---------------
some trashes CID: T17654, rest of trashes    T17654         
some trashes CID: 2876543298 rest of trashes 2876543298     
some trashes CID:2876543298 rest of trashes  2876543298     
some trashes CID: A-876                      A-876   

The WITH clause is for testing only.

The regular expression searches for CID: followed optionally by one space ?(if there is any number of spaces, replace the question mark with a star). Then, in parentheses, we have [^ ,]* which means the maximum number of consecutive characters other than space and comma. This thing in parentheses (technically a "capture group") is the substring you are after. To return this capture group, use the sixth argument to regexp_substr - it's the first capture group (the only one, and therefore the first), and 1 as the sixth argument means return the first capture group.