StringUtils Package - String Manipulation Functions

PL/SQL Package: Exercise-2 with Solution

Write a PL/SQL code to develop a package that includes procedures and functions to perform various string manipulations, such as reversing a string and counting the occurrence of a substring.

Sample Solution:

PL/SQL Code:

CREATE OR REPLACE PACKAGE StringUtils IS
  FUNCTION ReverseString(input_string IN VARCHAR2) RETURN VARCHAR2;
  FUNCTION SubstringCount(input_string IN VARCHAR2, substring IN VARCHAR2) RETURN NUMBER;
END StringUtils;
/
CREATE OR REPLACE PACKAGE BODY StringUtils IS
  FUNCTION ReverseString(input_string IN VARCHAR2) RETURN VARCHAR2 IS
reversed_stringVARCHAR2(32767);
  BEGIN
    IF input_string IS NULL THEN
      RETURN NULL;
    END IF;
    FOR i IN REVERSE 1..LENGTH(input_string) LOOP
reversed_string := reversed_string || SUBSTR(input_string, i, 1);
    END LOOP;
    RETURN reversed_string;
  END ReverseString;
  FUNCTION SubstringCount(input_string IN VARCHAR2, substring IN VARCHAR2) RETURN NUMBER IS
cnt NUMBER := 0;
position NUMBER := 1;
  BEGIN
    IF input_string IS NULL OR substring IS NULL THEN
      RETURN 0;
    END IF;
    LOOP
position := INSTR(input_string, substring, position);
      EXIT WHEN position = 0;
cnt := cnt + 1;
position := position + 1;
    END LOOP;
    RETURN cnt;
  END SubstringCount;
END StringUtils;
/

Sample Output:

Package created.
Package Body created

Flowchart:

To execute the package:

DECLARE
reversed_stringVARCHAR2(32767);
substring_count NUMBER;
BEGIN
reversed_string := StringUtils.ReverseString('Hello, World!');
  DBMS_OUTPUT.PUT_LINE('Reversed string: ' || reversed_string);
substring_count := StringUtils.SubstringCount('Hello, Hello, World!', 'Hello');
  DBMS_OUTPUT.PUT_LINE('Substring count: ' || substring_count);
END;
/

Sample Output:

Statement processed.
Reversed string: !dlroW ,olleH
Substring count: 2

Flowchart:

Explanation:

The said code in Oracle's PL/SQL package that provides convenient functions for string manipulation tasks such as reversing a string and counting the occurrences of a substring.

The PL/SQL package StringUtils consists two functions that facilitate string manipulation operations.

The ReverseString Function accepts an input parameter 'input_string' of type VARCHAR2 and returns a reversed version of the string then checks for NULL.

A loop that iterates in reverse order from the length of 'input_string' down to 1 and appends each character from 'input_string' to the 'reversed_string' variable and returns the resulting reversed string.

The SubstringCount Function accepts two input parameters 'input_string' of type VARCHAR2 that returns the count of occurrences of 'substring' within 'input_string' and 'substring' of type VARCHAR2.

Returns 0 either 'input_string' or 'substring' is NULL. A loop that iteratively searches for 'substring' within 'input_string' and the occurrences count by the 'cnt' variable and exits when no more occurrences are found.

Returns the final count of 'substring' occurrences.

Previous: Factorial Calculation and Prime Number Check.
Next: Input validation package in PL/SQL.