Locate a substring in an Oracle string

advertisements

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.


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;