Escape '%' while using Oracle's REGEXP_LIKE function [duplicate]

Question

I tied searching on SO about my issue but didn't find one. I apologize if it is a duplicate.

Let's say I have a table as shown below:

|ACCOUNT | FEES_DETAILS        |
================================
|AX001   | AOF=£20.5,VAT=25%   |
|AX009   | AOF=11.25%,VAT=12.5%|

I want to fetch all the rows when FEES_DETAILS column has a %-based AOF(in the above example, I need to fetch the second record - AX009 as AOF=11.25%).

I am executing the following query but it is not returning any row(s):

SELECT * FROM ACCOUNT_FEES_DATA WHERE REGEXP_LIKE(FEES_DETAILS, 'AOF\s*=\s*\d+(?:\.\d+)?%');

I thought % at the end of my regular expression AOF\s*=\s*\d+(?:\.\d+)?% is being treated as a special character so I tried to escape it with a \. But that too didn't return any rows. Is there any other way of escaping a % in REGEXP_LIKE function? Can anyone help me with this?

Is your fees_details have only two values? I.e. AOF and VAT? — Popeye
– Popeye, Commented Jan 28, 2020 at 9:33
Actually, your query should not be interested in separate parts of the fees_details, because if they were of interest, you'd have a fees_details table instead with the values already stored in separate rows. So, what you're dealing with here may be a flaw in the database design. — Thorsten Kettner
– Thorsten Kettner, Commented Jan 28, 2020 at 9:43

Naveen Kumar · Accepted Answer · 2020-01-28 09:42:04Z

0

Try this:

select * from test where REGEXP_LIKE(fees,'AOF=\d+.\d+%,');

answered Jan 28, 2020 at 9:42

Naveen Kumar

1,9961 gold badge9 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Escape '%' while using Oracle's REGEXP_LIKE function [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related