Nandu Nandu - 2 months ago 8
SQL Question

How to find the length of an input string without using standard length function in PL/SQL?

Is there a way to know the string length by writing PL/SQL function, without using LENGTH()?

Answer
CREATE OR REPLACE FUNCTION mylength (p_text IN VARCHAR2)
   RETURN INTEGER
AS
   l_ret   INTEGER := 0;
BEGIN
   WHILE SUBSTR (p_text, l_ret + 1, 1) IS NOT NULL
   LOOP
      l_ret   := l_ret + 1;
   END LOOP;

   RETURN l_ret;
END mylength;

BEGIN
   DBMS_OUTPUT.put_line ('ABC: ' || mylength ('ABC'));
   DBMS_OUTPUT.put_line ('ABCDEFG: ' || mylength ('ABCDEFG'));
   DBMS_OUTPUT.put_line ('empty: ' || mylength (''));
   DBMS_OUTPUT.put_line ('null: ' || mylength (NULL));
END;

ABC: 3
ABCDEFG: 7
empty: 0
null: 0