Q:

Oracle SQL String Manipulation

My field contains short codes that I want to access, such as C-COR3.
The issue is some records have additional information (F and H with numbers). An example is C-COR3 F1.54H19, I only care about C-COR3. Anything after "F" I want to ignore.
Code below works, but only if I hard-code the full F1.54H19. I want to use wildcards to abstract this for other occurrences that have F and H info in the field. (Ex C-R3 F0.18H18 -> C-R3 or C-COR3 F0.23H8.5 -> C-COR3), note varying short code string lengths. 
/* Translates C-COR3 F1.54H19 to C-COR3. */
select distinct SUBSTR(lud_code_short,1,INSTR(lud_code_short, 'F1.54H19')-2) 
from rep_dba.mytable

I've read that SUBSTR does not allow wildcards, but have had no luck trying my hand at REGEXP_INSTR and REGEX_SUBSTR instead. Any help appreciated. 

A:

Assuming that the "code" is always the first continuous sequence of non-space characters (and that there are no leading spaces - if there are, that's easy to handle), you could do something like this. Note the str || ' ' in the call to instr() - that takes care of the case when the input string has no spaces in it to begin with. Also notice the last input - since there are no spaces anywhere, the output is the same as the input. (Showing that if the "code" is not always separated from the "additional information" by at least one space, the solution would not work.)
with
  test_data (str) as (
    select 'C-COR3 F14H2.5' from dual union all
    select 'C-AB3'          from dual union all
    select null             from dual union all
    select 'C-AB2F14H2.5'   from dual
  )
select str, substr(str, 1, instr(str || ' ', ' ') - 1) as code
from   test_data
;

STR            CODE          
-------------- --------------
C-COR3 F14H2.5 C-COR3        
C-AB3          C-AB3         

C-AB2F14H2.5   C-AB2F14H2.5 

