I need to use SQL to extract only the YES or NO from the below strings:

'5^hgf^A^6^YES@1^g'
'H^jfd^k^i^NO@2^ojg'

So far I tried this regex:

Regexp(column_name,[^^|],1,4)

We need characters between @ and ^, specifically YES and NO.

How can I do this?

CodePudding user response：

If real data really looks like sample data you posted, then it is the 5th "word" you're looking for. In that case, this might do:

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 uses regular expression and looks prettier
• result_2 uses substr instr combination, extracting string between 4th ^ character and @

Have a look at the fiddle.

CodePudding user response：

One option is to compare length of string with length of string where YES/NO are removed if present...

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     */