PL/SQL Fundamentals Exercises: PL/SQL block to describe the usage of LIKE operator

PL/SQL Fundamentals: Exercise-16 with Solution

Write a PL/SQL block to describe the usage of LIKE operator including wildcard characters and escape character.

In the following example a procedure pat_match with two arguments test_string and pattern compares whether the test_string matching with the pattern and returns TRUE or FALSE according to the matching.

PL/SQL Code:

DECLARE
  PROCEDURE pat_match (
    test_string   VARCHAR2,
    pattern       VARCHAR2
  ) IS
  BEGIN
    IF test_string LIKE pattern THEN
      DBMS_OUTPUT.PUT_LINE ('TRUE');
    ELSE
      DBMS_OUTPUT.PUT_LINE ('FALSE');
    END IF;
  END;
BEGIN
  pat_match('Blweate', 'B%a_e');
  pat_match('Blweate', 'B%A_E');
END;
/

Sample Output:

TRUE
FALSE

Statement processed.

0.00 seconds

Flowchart:

In the following example a procedure pat_escape with the arguments mar_achiv compares whether the mar_achiv containing any percent ( % ) sign or underscore ( _ ) and returns TRUE or FALSE according to the matching. To search for the percent sign or underscore, an escape character ( backslash i.e. '\' ) have to define and put it before the percent sign or underscore. Uses the backslash as the escape character the percent sign in the string does not act as a wildcard.

PL/SQL Code:

DECLARE
  PROCEDURE pat_escape (mar_achiv VARCHAR2) IS
  BEGIN
    IF mar_achiv LIKE '70\%  out of 100!' ESCAPE '\' THEN
      DBMS_OUTPUT.PUT_LINE ('TRUE');
    ELSE
      DBMS_OUTPUT.PUT_LINE ('FALSE');
    END IF;
  END;
BEGIN
  pat_escape('Go and try your best');
  pat_escape('70%  out of 100!');
END;
/

Sample Output:

FALSE
TRUE

Statement processed.

0.00 seconds

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL block to describe the usage of NULL values in equal comparison, unequal comparison and NOT  NULL equals NULL comparison.
Next: PL/SQL Control Statement Exercises Home.