How to iterate over binary string in Oracle?-CodePudding


v_length number(10);
v_out varchar2(20);
Begin
v_length := length(:IN_string);
for i in 1..v_length
Loop
v_out  := substr(:IN_string,i,1) ;
DBMS_OUTPUT.PUT_LINE(v_out);
End loop;

When I pass '00111000' as input to IN_STRING variable , it trims the string and behaves very unusually.Please suggest some good approaches to iterate over binary strings like this

CodePudding user response：

Shouldn't behave unusual, unless datatype of in_string variable is NUMBER (then leading zeros don't have any meaning) - switch to VARCHAR2.

Illustration:

NUMBER variable datatype
value you enter
result - really, missing leading zeros

Otherwise, it works OK (this is SQL*Plus so I used substitution variable):

SQL> DECLARE
  2     v_length  NUMBER (10);
  3     v_out     VARCHAR2 (20);
  4  BEGIN
  5     v_length := LENGTH ( '&&in_string');
  6
  7     FOR i IN 1 .. v_length
  8     LOOP
  9        v_out := SUBSTR ( '&&in_string', i, 1);
 10        DBMS_OUTPUT.PUT_LINE (v_out);
 11     END LOOP;
 12  END;
 13  /
Enter value for in_string: 00111000
0
0
1
1
1
0
0
0

PL/SQL procedure successfully completed.

Another option (if you're interested in it) doesn't require PL/SQL:

SQL>     SELECT SUBSTR ( '&&in_string', LEVEL, 1) val
  2        FROM DUAL
  3  CONNECT BY LEVEL <= LENGTH ( '&&in_string');

V
-
0
0
1
1
1
0
0
0

8 rows selected.

SQL>

CodePudding user response：

Your code works so long as you pass in a VARCHAR2 data type (and not a NUMBER).

You can also tidy up the code passing in the bind variable only once and using CONSTANTs to hold the values that are constant:

DECLARE
  c_string CONSTANT VARCHAR2(200) := :in_string;
  c_length CONSTANT PLS_INTEGER := LENGTH(c_string);
  v_out    CHAR(1);
BEGIN
  FOR i IN 1..c_length
  LOOP
    v_out := SUBSTR(c_string,i,1) ;
    DBMS_OUTPUT.PUT_LINE(v_out);
  END LOOP;
END;
/

Which outputs:

db<>fiddle here