CODEWITHSUNDEEP
oracle-databaseplsql
heferpi
heferpi

Reputation: 11

How can I do a procedure that tells me the size of the substrings?

I am learning plsql and I have some doubts.

How do I get the size of all substrings in the string? The way I did it, I'm only managing to return the size of just the first substring.

set serveroutput on;
create or replace procedure pr_size (value varchar2)
as
answer varchar2 (300): = '';

begin
    for x in 1 .. length (value) loop
    cont: = x;
        if substr (value, x, 1) = ''
            then dbms_output.put_line ('Size:' || length (answer));
                 exit;
            else answer: = answer || substr (value, x, 1);
        end if;
    end loop;
end;
 
call pr_size ('Word size calculation test');

Upvotes: 1

Views: 59

Answers (1)

Aman Singh Rajpoot
Aman Singh Rajpoot

Reputation: 1479

you can do something like this

DECLARE 
     
 type string_table is table of VARCHAR2(100);
 st string_table;
    
BEGIN 
  
  SELECT regexp_substr('THIS IS TEST', '[^ ]+', 1, LEVEL) BULK COLLECT INTO st FROM dual CONNECT by level <= regexp_count('THIS IS TEST', '[^ ]+');
  
  for i in 1..st.COUNT
    LOOP 
    dbms_output.put_line(length(st(i)));
  END LOOP;
  
END;

If you want a string of length like in your code then

CREATE OR REPLACE PROCEDURE pr_size (value varchar2)
AS
  type string_table is table of VARCHAR2(100);
  st string_table;
  answer VARCHAR2(100);

BEGIN   
  SELECT regexp_substr(value, '[^ ]+', 1, LEVEL) BULK COLLECT INTO st FROM dual CONNECT by level <= regexp_count(value, '[^ ]+');
  
  for i in 1..st.COUNT
    LOOP 
    answer := length(st(i))||' '||answer;
  END LOOP;
  dbms_output.put_line(answer);
END;

If you want to use your logic then I have corrected your logic

DECLARE 
    
  value VARCHAR2(100) := 'this is test for word length';
  
  answer VARCHAR2(100);
  cont PLS_INTEGER := 0;
BEGIN   
  for x in 1 .. length (value) loop
    
        if substr (value, x, 1) = ' '  then
        dbms_output.put_line ('Size:' || cont);
        cont := 0;
        ELSE 
          cont := cont + 1;
        end if;
    
    end loop;
    dbms_output.put_line ('Size:' || cont);
END;

Upvotes: 2

Related Questions