如果真实的数据看起来真的像你发布的样本数据，那么它就是你要找的第五个“词”。在这种情况下，这可能会：

with test (col) as 
  (select '5^hgf^A^6^YES@1^g' from dual union all
   select 'H^jfd^k^i^NO@2^ojg' from dual
  )
select col,
  regexp_substr(col, '\w+', 1, 5) result_1,
  substr(col, instr(col, '^', 1, 4) + 1,
              instr(col, '@') - instr(col, '^', 1, 4) - 1
        ) result_2
from test;

字符串

• result_1使用正则表达式，看起来更漂亮
• result_2使用substr + instr组合，提取第4个^字符和@之间的字符串

看看fiddle。

一个选项是比较字符串的长度与字符串的长度，其中如果存在，则删除YES/NO..

WITH
    tbl AS  --  S a m p l e   D a t a :
        (   Select '5^hgf^A^6^YES@1^g' "TXT" From Dual Union All
            Select 'H^jfd^k^i^NO@2^ojg'  "TXT" From Dual Union All
            Select '5^hgf^A^6^YES@1^g^NO@' "TXT" From Dual Union All
            Select 'H^jfd^k^i^XX@2^ojg'  "TXT" From Dual
        )
--  M a i n    S Q L :
Select  TXT, Case When Length(TXT) > Length(Replace(TXT, '^YES@')) And Length(TXT) > Length(Replace(TXT, '^NO@')) Then 'YES_NO'
                  When Length(TXT) > Length(Replace(TXT, '^YES@')) Then 'YES'
                  When Length(TXT) > Length(Replace(TXT, '^NO@')) Then 'NO'
             Else 'None'
             End "FLAG"
From    tbl
/*    R e s u l t :
TXT                   FLAG  
--------------------- ------
5^hgf^A^6^YES@1^g     YES   
H^jfd^k^i^NO@2^ojg    NO    
5^hgf^A^6^YES@1^g^NO@ YES_NO
H^jfd^k^i^XX@2^ojg    None     */

字符串

这是对你的问题的最佳回答：

select output from (Select Rownum r, 'YES'  as output
From dual
Connect By Rownum <=(SELECT REGEXP_COUNT ('5^hYESgf^A^6^YES@1^g''H^jfd^k^i^NO@2^ojg', 'YES')
FROM dual) 
union
Select Rownum r, 'NO'  as output
From dual
Connect By Rownum <=(SELECT REGEXP_COUNT ('5^hYESgf^A^6^YES@1^g''H^jfd^k^i^NO@2^ojg', 'NO')
FROM dual))
;

字符串

尝试以下 * 捕获模式 *。

(?:.+?\^){4}([^@]+)

字符串