When APEX_STRING Wants to Be as Cool as Linux!

Let’s be real. One day in the middle of a project, I caught myself wishing for something like good old Linux grep right inside Oracle APEX. “What if I could just scan a list of strings with a regex and spot the odd one out?”
Well, Oracle had the same thought — and they packed not just one, but three awesome versions of GREP inside the APEX_STRING package.

Let’s dive in and explore what these GREP functions do, how they work, and how you can use them like a pro.

---

What Is APEX_STRING.GREP?

The GREP functions in the APEX_STRING package allow you to search through arrays of strings using regular expressions, just like the classic grep command-line tool.

Primary Use Cases:

• Filtering strings that match specific patterns

• Validating inputs like emails or phone numbers

• Detecting anomalies in bulk data

• Extracting only relevant entries from datasets

---

The Three GREP Function Signatures

1. GREP(p_table IN APEX_T_VARCHAR2, p_pattern IN VARCHAR2) RETURN APEX_T_VARCHAR2

Description:
Takes an array of strings (APEX_T_VARCHAR2) and returns only those strings that match the given regex pattern.

Example:

DECLARE
  l_input apex_t_varchar2 := apex_t_varchar2('apple', 'banana', 'apricot', 'cherry');
  l_result apex_t_varchar2;
BEGIN
  l_result := apex_string.grep(l_input, '^a');
  -- Returns: 'apple', 'apricot'
  FOR i IN 1 .. l_result.COUNT LOOP
    dbms_output.put_line(l_result(i));
  END LOOP;
END;

---

2. GREP(p_table IN APEX_T_VARCHAR2, p_pattern IN VARCHAR2, p_match IN BOOLEAN) RETURN APEX_T_VARCHAR2

Description:
Same as the first version but with a p_match flag:

• If TRUE: returns only matching strings

• If FALSE: returns only non-matching strings

Example:

DECLARE
  l_input apex_t_varchar2 := apex_t_varchar2('cat', 'dog', 'cow', 'camel');
  l_result apex_t_varchar2;
BEGIN
  l_result := apex_string.grep(l_input, '^c', FALSE);
  -- Returns: 'dog'
  FOR i IN 1 .. l_result.COUNT LOOP
    dbms_output.put_line(l_result(i));
  END LOOP;
END;

---

3. GREP(p_table IN APEX_T_VARCHAR2, p_pattern IN VARCHAR2, p_match IN BOOLEAN, p_flags IN VARCHAR2) RETURN APEX_T_VARCHAR2

Description:
Like the second version, but with a fourth parameter: p_flags for regex options.

• 'i': case-insensitive

• 'm': multiline

• 'n': ignores line terminators

Example:

DECLARE
  l_input apex_t_varchar2 := apex_t_varchar2('Oracle', 'oracle', 'ORACLE', 'Postgres');
  l_result apex_t_varchar2;
BEGIN
  l_result := apex_string.grep(l_input, '^oracle$', TRUE, 'i');
  -- Returns: 'Oracle', 'oracle', 'ORACLE'
  FOR i IN 1 .. l_result.COUNT LOOP
    dbms_output.put_line(l_result(i));
  END LOOP;
END;

---

Two Real-World Examples (Actually Useful!)

Example 1: Filter Valid Emails from a List

DECLARE
  l_emails apex_t_varchar2 := apex_t_varchar2(
    'user@example.com',
    'not-an-email',
    'hello@domain.net',
    'wrong@email',
    'me@site.org'
  );
  l_valid_emails apex_t_varchar2;
BEGIN
  l_valid_emails := apex_string.grep(
    l_emails,
    '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$',
    TRUE
  );

  FOR i IN 1 .. l_valid_emails.COUNT LOOP
    dbms_output.put_line('✅ Valid: ' || l_valid_emails(i));
  END LOOP;
END;

Goal: Keep only properly formatted email addresses.

---

Example 2: Find All Values Containing the Number “13”

DECLARE
  l_ids apex_t_varchar2 := apex_t_varchar2('0013', '1234', '8713', '9000', '13AB', 'AB13');
  l_filtered apex_t_varchar2;
BEGIN
  l_filtered := apex_string.grep(l_ids, '13');

  FOR i IN 1 .. l_filtered.COUNT LOOP
    dbms_output.put_line('🎯 Found: ' || l_filtered(i));
  END LOOP;
END;

Goal: Identify IDs that contain the suspicious number “13”.

---

Pro Tips

• Combine GREP with APEX_STRING.SPLIT to search through comma-separated strings.

• Use p_flags => 'i' for case-insensitive matching.

• Use BULK COLLECT to load data from tables into APEX_T_VARCHAR2 arrays before applying GREP.

---

Summary

The GREP functions in APEX_STRING are powerful, elegant, and super handy for working with string arrays in Oracle APEX. Whether you're validating emails, detecting patterns, or filtering logs — GREP helps you slice through the noise like a regex ninja.