Locate a sub-string within a string Oracle

Question

I am trying to locate a substring within a string. Everything I find on the net is locating its position. I don't care about the position and given the position on every row floats I cant say regexp_instr(X, 10, 5).

I am looking to say if X in Column String then give me a new column with only the string I asked for. If not Give me Y.

The issue is that 1 of 10 strings are within Column String and I am trying to extract them out in 1 to 2 steps as a separate column.

Can this be done? Thanks.

Answer 1

maybe something like... But I'm unclear if you want one column, multiple columns or if you want first result or all results that match...

So if column contains 'CAT' and you're looking for Z, C, A, T... What results should be in the resulting column? No Z... just C or CAT or what?

SELECT CASE WHEN INSTR('CAT','Z')>0 THEN 'Z' 
            WHEN INSTR('CAT','C')>0 THEN 'C'
            WHEN INSTR('CAT','A')>0 THEN 'A'
            WHEN INSTR('CAT','T')>0 THEN 'T'
            ELSE 'Not in' end from dual;

Answer 2

To search a string for another string and return the searched for string when found or a fixed string if not found you have several options. The instr and regexp_instr methods rely on the fact that when not found they return an index of zero (0) while the regexp_substr method returns null when not found otherwise it returns the string that matches the search pattern:

with dta as (
  select 'this is a test' str from dual union all
  select 'every good boy does fine' from dual union all
  select 'testing one two three' from dual
), fnd as (
  select 'test' fnd, 'not found' fail from dual union all
  select 'good' fnd, 'not good' fail from dual
)
select fnd
     , str
     , case instr(str, fnd) when 0 then fail else fnd end result
     , case regexp_instr(str,fnd) when 0 then fail else fnd end result2
     , nvl(regexp_substr(str, fnd), fail) result3
  from dta, fnd
  order by 1, 2;