How to use REGEXP_REPLACE to remove parameters from function?

Question

Image Here

I have a column that stores C# codes, how do I remove the last 3 parameters of the "FunctionA"? Note that the column contains multiple functions, but I only need to replace "FunctionA" using PL/SQL, I know REGEXP_REPLACE might do the trick, but I can't seem to find a way to match/replace it.

Before:

Test=FunctionA(varID, 1234,"", A.B,"","","last");

Test=FunctionA(varID, 9876,"", C.D);

Test=FunctionB(varID, 5555,"", E.F,"","","last");

After:

Test=FunctionA(varID, 1234,"", A.B);

Test=FunctionA(varID, 9876,"", C.D);<- should not affect this

Test=FunctionB(varID, 5555,"", E.F,"","","last");<- should not affect this

I would like to add that the function's parameter values varies for each record, hence I can't use REPLACE function; I need some sort of pattern matching and remove the last 3 parameter for only "FunctionA" — Wakaboom
– Wakaboom, Commented May 25, 2018 at 10:15
Please edit your original post and include in plain text the "before" data as shown in the image. As a rule, never use images as people trying to help cannot copy/paste to get sample data. Can the string you need to manipulate occur more than once in the chunk of data? Does that data include carriage return/line feeds or is it one long string? — Gary_W
– Gary_W, Commented May 25, 2018 at 13:33

Tim Biegeleisen · Accepted Answer · 2018-05-25 10:31:59Z

1

Try finding this pattern:

(,[^,]*,[^,]*,[^,]*\);)$

And then replace with just );. Here is a sample query:

SELECT
    REGEXP_REPLACE ('Test=FunctionA(varID, 1234,"", A.B,"","","last");',
        '(,[^,]*,[^,]*,[^,]*\);)$', ');') AS output
FROM dual
WHERE col LIKE 'Test=FunctionA(%'

Test=FunctionA(varID, 1234,"", A.B);

Demo

Edit: I added a WHERE clause which checks the function name.

edited May 25, 2018 at 10:31

answered May 25, 2018 at 10:19

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

7 Comments

Wakaboom Over a year ago

Thanks its very close, but it's affecting all other functions in record example below... SELECT REGEXP_REPLACE ('Test=FunctionA(varID, 1234,"", A.B,"","","last"); Test=FunctionB(varID, 1234,"", A.B,"","","last"); ','(,[^,]*,[^,]*,[^,]*);)$', ');') AS output FROM dual how can you target just "FunctionA"?

Tim Biegeleisen Over a year ago

@Wakaboom There might be a way to alter the pattern I used in REGEXP_REPLACE but honestly I might just add a WHERE clause which checks the function name to play it safe.

Wakaboom Over a year ago

Agree the simple WHERE clause suffice, <hit myself in the head>. So far so good, still playing around with the regex which you have giving, yet to resolve in actual environment. Will post a solution once I found it.

Tim Biegeleisen Over a year ago

Will post a solution once I found it ... I already found it, at least based on your question. If not, then should edit your question.

Wakaboom Over a year ago

SELECT REGEXP_REPLACE ( 'Test=FunctionA(varID, 1234,"", A.B,"","","last"); Test=FunctionB(varID, 1234,"", A.B,"","","last"); Test=FunctionA(varID, 1234,"", A.B,"","","last");', '([FunctionA](varID, 1234,"",[^,]A.B,[^,]*,[^,]*,[^,]*);)', ');') AS output FROM dual I'm getting the below which is wrong... Test=Function); Test=FunctionB(varID, 1234,"", A.B,"","","last"); Test=Function); How can I get the below by modifying the above script?.. Test=FunctionA(varID, 1234,"", A.B); Test=FunctionB(varID, 1234,"", A.B,"","","last"); Test=FunctionA(varID, 1234,"", A.B);

|

Collectives™ on Stack Overflow

How to use REGEXP_REPLACE to remove parameters from function?

1 Answer 1

Demo

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related