CODEWITHSUNDEEP
oracle-databaseplsql
BeenThereDoneThat
BeenThereDoneThat

Reputation: 5

While loops with Strings not Integers

I'm having trouble finding any information on oracle plSQL while loops with strings online. All seem to be integer. When doing my research i feel i understand the integer aspect of while loops in plSQL but no sites that i have visited touched on or had examples of While Loops using strings.

For example: I can use a For loop to print individual letters from the word 'text' but what would stop me from using a While loop to get the same output? 

DECLARE
  c1 Number:= 1;
  c2 Varchar2(4);
BEGIN
  FOR c1 in 1..4
  LOOP
    SELECT substr('text' , c1 , 1 ) into c2 from dual;
    dbms_output.put_line(c2);
  END LOOP;
END;

If someone could explain how one would print a individual character or even the whole string with a while loop; or possibly point me in the right direction in terms of where to research example while loops with strings online.

Thank you.

Upvotes: 0

Views: 467

Answers (3)

user5683823
user5683823

Reputation:

You can write a WHILE loop entirely based on characters - no need for a counter of any kind. Something like this:

declare
  txt varchar2(100);
begin
  txt := 'my text';
  while txt is not null loop
    dbms_output.put_line(substr(txt, 1, 1));  -- or whatever else you need to do
    txt := substr(txt, 2);
  end loop;
end;
/


m
y

t
e
x
t


PL/SQL procedure successfully completed.

Upvotes: 3

Popeye
Popeye

Reputation: 35900

You can use SUBSTR in WHILE loop directly as follows:

SQL>
SQL> set serverout on
SQL> DECLARE
  2      C1   NUMBER := 1;
  3      C2   VARCHAR2(10) := 'TEXT';
  4  BEGIN
  5      WHILE SUBSTR(C2, C1, 1) IS NOT NULL LOOP
  6          DBMS_OUTPUT.PUT_LINE(SUBSTR(C2, C1, 1));
  7          C1 := C1 + 1;
  8      END LOOP;
  9  END;
 10  /
T
E
X
T

PL/SQL procedure successfully completed.

SQL>

Cheers!!

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Yes, it can be written using a WHILE loop. The crucial thing is the length function and the counter. Also, you don't need a select query.

SET SERVEROUTPUT ON

DECLARE
     c1 NUMBER := 1;
     txt VARCHAR2(20) := 'text';
BEGIN
     WHILE c1 <= length(txt) LOOP  
          dbms_output.put_line(substr(txt,c1,1));
          c1 := c1 + 1; --increment the counter
     END LOOP;
END;
/

Result

t
e
x
t

PL/SQL procedure successfully completed.

Upvotes: 2

Related Questions