PL/SQL Remove Characters While Loop

Question

Im working on an assignment, the goal being to write a procedure that has 2 inputs a string and a char and remove the char from the string.

Example: RemoveAll(123-456-789, '-') will output: 123456789.

create or replace procedure MyRemoveAll 
        (p_text varchar2, p_char_1 char) as
        v_temp varchar2(100);

   BEGIN

    v_temp := p_text;

    WHILE instr(v_temp, p_char_1) != 0 LOOP
        v_temp := substr(v_temp, 1, instr(v_temp, p_char_1)-1)
            || substr(p_text, instr(v_temp, p_char_1)+1);
        END LOOP;   

    dbms_output.put_line (v_temp);

    END;
   /


   exec MyRemoveAll('123456789', '*');
   exec MyRemoveAll('123-456789', '-');
   exec MyRemoveAll('123*456*789', '*');

I don't get any errors when the procedure is created, and the first two executions work correctly. I've tried just running: dbms_output.put_line (instr(123*456*789, *));

Which gave me an output of 4. So it should register for the condition of the while loop, but SQL Plus just stops at the loop. (Like it's doing an infinite loop, but it isn't).

Anyone have any ideas?

a string and a char - a char is just a special kind of string, that adds spaces to pad the value out to its maximum length. There is rarely any good use for this and in practice it causes more problems than it solves, which it recommended not to use it, ever. — William Robertson
– William Robertson, Commented Oct 21, 2018 at 18:51

Bobby Durrett · Accepted Answer · 2018-10-10 23:06:50Z

1

I think you need to replace p_text with v_temp in your second substr call. That way each time you loop through you are working with the updated v_temp and not the original p_text.

Here is what it would look like:

create or replace procedure MyRemoveAll 
        (p_text varchar2, p_char_1 char) as
        v_temp varchar2(100);

BEGIN

 v_temp := p_text;

 WHILE instr(v_temp, p_char_1) != 0 LOOP

     v_temp := substr(v_temp, 1, instr(v_temp, p_char_1)-1)
         || substr(v_temp, instr(v_temp, p_char_1)+1);
END LOOP;   

 dbms_output.put_line (v_temp);

 END;
/

All three of your examples work with this code.

Bobby

answered Oct 10, 2018 at 23:06

Bobby Durrett

1,30312 silver badges20 bronze badges

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

2 Comments

Andrew Crider Over a year ago

Gah. I see it now. That's what I get for copy + pasting code from a previous assignment. Thank you!

dcdum2018 Over a year ago

hi, can you please take a look at my question? it is similar to trimming last characters. thank you stackoverflow.com/questions/69834411/…

Collectives™ on Stack Overflow

PL/SQL Remove Characters While Loop

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related