selected splitted value in oracle-CodePudding

I have two columns A, B in oracle where A value has values like that xx-target-xx xx any data but target is exists

A 
--------
xx-target-xx
xx-target

i neet to return only 'target' from text i tired this

select TRIM(substr(A, 0, instr(A, '-') - 1))  from mytable

but the result returns xx not target

CodePudding user response：

Use REGEXP_SUBSTR. You want the second string of any characters except the minus sign:

select a, regexp_substr(a, '[^-] ', 1, 2) from mytable;

Using INSTR and SUBSTR instead is a tad more complicated, but possible of course:

select a, substr(a,
                 instr(a, '-')   1,
                 instr(a || '-', '-', 1, 2) - instr(a, '-') - 1
                ) as value
from mytable;

Demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=e75b878bbd6300e9207cd698bb3029ec