how to apply escape character (%) in substr (oracle sql)?

Question

I've tried to extract information before a percentage sign but can't figure out how. My table looks like this:

NAME
----------
20% on xy
----------
buy 2 get 1 free
----------
incentive 15% off

--> From this list I only want to see in a seperate column the 20% in the first row and the 15% in the third row

This is what I've tried:

SELECT case when NAME like '%\%%' ESCAPE '\' 
            then substr('%\%%' ESCAPE '\', -2) 
            else 0 
        end as xy 
  FROM Data d

I hope you understand what I mean... Can anyone help me with this? Many thanks in advance!!

And sorry for the wrong formatting, I'm new..

Answer 1

I think your "LIKE" part is correct but substr needs to be fixed:

    SELECT case when NAME like '%\%%' ESCAPE '\'
              then reverse(substr(reverse(NAME), instr(reverse(NAME), '%'), 3 ))  
           else '0' 
           end as xy 
FROM Data d