Splitting a colon-separated list of IDs, joining values into one line, filling placeholders in a message, or pulling email addresses out of a block of text are everyday jobs in Oracle APEX code. APEX_STRING handles strings and string lists, and APEX_STRING_UTIL finds things inside text and turns it into slugs, file sizes, and diffs. Both work in PL/SQL, and most of their functions also work in SQL through table().
This guide covers both packages with tested examples and their real output, including two behaviors in APEX 26.1 that catch people out: GREP returns only the matched part of each value, and FIND_IDENTIFIERS doubles a prefix that ends with a dash.
Quick Reference
| Task | Function |
|---|---|
| Fill placeholders in a message | APEX_STRING.FORMAT |
| Split a string into a list | SPLIT, SPLIT_NUMBERS, SPLIT_CLOBS |
| Join a list into a string | JOIN, JOIN_CLOB, JOIN_CLOBS |
| Work with the old vc_arr2 type | STRING_TO_TABLE, TABLE_TO_STRING, TABLE_TO_CLOB |
| Add to, search, and filter lists | PUSH, INDEX_OF, GREP, SHUFFLE |
| Get initials for an avatar | GET_INITIALS |
| Use a list as a small key-value map | PLIST_PUT, PLIST_GET, PLIST_GET_KEY, PLIST_EXISTS, PLIST_DELETE, PLIST_PUSH, PLIST_TO_JSON_CLOB |
| Read a CLOB in pieces | NEXT_CHUNK |
| Extract search terms | GET_SEARCHABLE_PHRASES |
| Find emails, links, tags, and IDs in text | APEX_STRING_UTIL find functions |
| Make slugs, file sizes, and diffs | GET_SLUG, GET_DOMAIN, GET_FILE_EXTENSION, TO_DISPLAY_FILESIZE, REPLACE_WHITESPACE, DIFF |
How to Run These Examples
The examples ran in Oracle APEX 26.1. Most need nothing but the database: run them as your workspace schema in SQL Developer, SQLcl, SQL*Plus, or SQL Workshop with server output switched on. The output under each example is exactly what the database printed.
The first example also needs an APEX session, noted above it; create one with APEX_SESSION.CREATE_SESSION as shown in the guide to creating APEX sessions and managing session state from PL/SQL. It counts rows in orb_orders, a table of the Orbit Outfitters sample schema, which you can install from the orb_tables repository on GitHub.
APEX's string lists are the collection types apex_t_varchar2, apex_t_number, and apex_t_clob. They work in PL/SQL and, through table(), in SQL.
Formatting Messages
FORMAT
Returns a message with the placeholders %0 to %19 replaced by the values p0 to p19. %s takes the values in turn, %% is a percent sign, and %n a new line. p_max_length, 1000 by default, cuts each value to a length and marks the cut with a tilde. p_prefix is removed from the start of each line, so a message can be indented in your code.
Syntax:
apex_string.format(p_message in varchar2, p0 in varchar2 default null, ... p19 in varchar2 default null,
p_max_length in pls_integer default 1000, p_prefix in varchar2 default null) return varchar2This example needs a session of application 200, page 1, and the orb_orders table.
Example:
declare
l_count pls_integer;
begin
select count(*) into l_count from orb_orders where status = 'SHIPPED';
dbms_output.put_line(
apex_string.format(
p_message => 'User %0 sees %1 shipped orders in application %2.',
p0 => apex_application.g_user,
p1 => l_count,
p2 => apex_application.g_flow_id));
end;
/Output:
User ADMIN sees 32 shipped orders in application 200.
FORMAT also works in SQL. Here p_max_length cuts a long value and marks the cut:
Example:
select apex_string.format(
p_message => 'Customer: %0',
p0 => 'Wildflower Travel Company of the Pacific Northwest',
p_max_length => 20) as message
from dual;Output:
MESSAGE ------------------------------ Customer: Wildflower Travel C~
FORMAT is much easier to read and translate than a chain of || operators, and the length limit protects log messages from runaway values.
Splitting and Joining
SPLIT, SPLIT_NUMBERS, SPLIT_CLOBS, JOIN, JOIN_CLOB, and JOIN_CLOBS
SPLIT splits a string or a CLOB at a separator, which may be a regular expression, into an apex_t_varchar2; p_limit stops after a number of parts. SPLIT_NUMBERS returns numbers and SPLIT_CLOBS returns CLOB parts. JOIN joins a list into a string, JOIN_CLOB into a CLOB, and JOIN_CLOBS joins CLOBs.
Syntax:
apex_string.split(p_str in varchar2 | clob, p_sep in varchar2 default apex_application.LF,
p_limit in pls_integer default null) return apex_t_varchar2
apex_string.split_numbers(p_str in varchar2, p_sep in varchar2 default apex_application.LF) return apex_t_number
apex_string.split_clobs(p_str in clob, p_sep in varchar2 default apex_application.LF, p_limit in pls_integer default null) return apex_t_clob
apex_string.join(p_table in apex_t_varchar2 | apex_t_number, p_sep in varchar2 default apex_application.LF) return varchar2
apex_string.join_clob(p_table in apex_t_varchar2, p_sep in varchar2 default apex_application.LF, p_dur in pls_integer default dbms_lob.call) return clob
apex_string.join_clobs(p_table in apex_t_clob, p_sep in varchar2 default apex_application.LF, p_dur in pls_integer default dbms_lob.call) return clobIn SQL, SPLIT turns a delimited string into rows:
Example:
select column_value as tag
from table(apex_string.split('tents,backpacks,,stoves', ','));Output:
TAG --------- tents backpacks (null) stoves
The empty value between the two commas became a null row rather than disappearing, so filter it out if empty entries are not meaningful.
Example:
declare
l_ids apex_t_number;
l_words apex_t_varchar2;
l_parts apex_t_clob;
begin
l_words := apex_string.split('tent,stove;lamp', '[,;]'); -- a regular expression
dbms_output.put_line('regexp split: ' || apex_string.join(l_words, ' | '));
l_words := apex_string.split('a:b:c:d', ':', p_limit => 2);
dbms_output.put_line('limit 2: ' || apex_string.join(l_words, ' | '));
l_ids := apex_string.split_numbers('2282:2280:2279', ':');
dbms_output.put_line('numbers: ' || l_ids.count || ', sum ' || (l_ids(1) + l_ids(2) + l_ids(3)));
l_parts := apex_string.split_clobs(to_clob('line one' || chr(10) || 'line two'));
dbms_output.put_line('clobs: ' || l_parts.count || ', joined: ' || replace(apex_string.join_clobs(l_parts, ' / '), chr(10)));
dbms_output.put_line('join_clob: ' || apex_string.join_clob(apex_t_varchar2('ORD-12283', 'ORD-12280'), ', '));
end;
/Output:
regexp split: tent | stove | lamp limit 2: a | b:c:d numbers: 3, sum 6841 clobs: 2, joined: line one / line two join_clob: ORD-12283, ORD-12280
The default separator is a line feed, not a comma, so always pass one when splitting comma or colon lists. The most common use in APEX is turning a multi-select item's colon-separated value into rows, for example where id in (select column_value from table(apex_string.split_numbers(:P1_IDS, ':'))). For other approaches, see how to split a string in PL/SQL, and for the SQL side of joining, the LISTAGG string aggregation guide.
STRING_TO_TABLE, TABLE_TO_STRING, and TABLE_TO_CLOB
The same operations for the older PL/SQL table type apex_application_global.vc_arr2, which apex_application.g_f01 and older APIs use. The separator defaults to a colon. They replace the deprecated APEX_UTIL functions of the same names.
Example:
declare
l_tab apex_application_global.vc_arr2;
begin
l_tab := apex_string.string_to_table('Red:Olive:Sand'); -- the old PL/SQL table type
dbms_output.put_line('rows: ' || l_tab.count || ', second: ' || l_tab(2));
dbms_output.put_line('table_to_string: ' || apex_string.table_to_string(l_tab, ' - '));
dbms_output.put_line('table_to_clob: ' || replace(apex_string.table_to_clob(l_tab), chr(10), '\n'));
end;
/Output:
rows: 3, second: Olive table_to_string: Red - Olive - Sand table_to_clob: Red\nOlive\nSand
Working with Lists
PUSH, INDEX_OF, GREP, SHUFFLE, and GET_INITIALS
PUSH adds a value, or every value of another list, to the end of a list, with seven overloads for apex_t_varchar2, apex_t_number, and apex_t_clob. INDEX_OF returns a value's position, or null. GREP returns the values that match a regular expression, either the whole match or, with p_subexpression, part of it. SHUFFLE returns the list in random order. GET_INITIALS returns a name's initials, as avatars show them.
Syntax:
apex_string.push(p_table in out nocopy apex_t_varchar2, p_value in varchar2 | p_values in apex_t_varchar2)
apex_string.index_of(p_table in apex_t_varchar2, p_value in varchar2) return number
apex_string.grep(p_table in apex_t_varchar2, p_pattern in varchar2, p_modifier in varchar2 default null,
p_subexpression in varchar2 default '0', p_limit in pls_integer default null) return apex_t_varchar2
apex_string.shuffle(p_table in apex_t_varchar2) return apex_t_varchar2
apex_string.get_initials(p_str in varchar2, p_cnt in pls_integer default 2) return varchar2Example:
declare
l_list apex_t_varchar2 := apex_t_varchar2();
l_nums apex_t_number := apex_t_number();
begin
apex_string.push(l_list, 'Tents');
apex_string.push(l_list, apex_t_varchar2('Stoves', 'Lanterns', 'Tarps')); -- a whole table
apex_string.push(l_nums, 2282);
dbms_output.put_line('list: ' || apex_string.join(l_list, ', ') || ' - numbers: ' || l_nums.count);
dbms_output.put_line('index_of: ' || apex_string.index_of(l_list, 'Lanterns') || ', missing: ' || apex_string.index_of(l_list, 'Axes'));
dbms_output.put_line('grep: ' || apex_string.join(apex_string.grep(l_list, '^T.*'), ', '));
dbms_output.put_line('grep i: ' || apex_string.join(apex_string.grep(l_list, '^.*S$', 'i'), ', '));
dbms_output.put_line('shuffle: ' || apex_string.shuffle(l_list).count || ' elements in random order');
dbms_output.put_line('initials: ' || apex_string.get_initials('Wildflower Travel Co.') || ', '
|| apex_string.get_initials('Matthew Jones', 1));
end;
/Output:
list: Tents, Stoves, Lanterns, Tarps - numbers: 1 index_of: 3, missing: grep: Tents, Tarps grep i: Tents, Stoves, Lanterns, Tarps shuffle: 4 elements in random order initials: WT, M
Be careful with GREP: it returns what the pattern matched, not the whole value. With ^T alone, it would have returned just T twice. To get whole values back, make the pattern match the whole value, as ^T.* does.
Property Lists: PLIST_PUT, PLIST_GET, PLIST_GET_KEY, PLIST_EXISTS, PLIST_DELETE, PLIST_PUSH, and PLIST_TO_JSON_CLOB
A property list is an apex_t_varchar2 holding keys and values in turn, a small map without the need for a record type. PLIST_PUT sets a key's value, PLIST_PUSH appends a pair without checking for an existing key, PLIST_GET returns a value, PLIST_GET_KEY returns the key of a value, PLIST_EXISTS tells whether a key exists, and PLIST_DELETE removes one. PLIST_TO_JSON_CLOB turns the list into a JSON object.
Example:
declare
l_plist apex_t_varchar2 := apex_t_varchar2(); -- a property list: key, value, key, value
begin
apex_string.plist_put(l_plist, 'status', 'SHIPPED');
apex_string.plist_put(l_plist, 'carrier', 'UPS');
apex_string.plist_put(l_plist, 'status', 'DELIVERED'); -- replaces the value
apex_string.plist_push(l_plist, 'note', 'left at door'); -- adds without checking
dbms_output.put_line('status: ' || apex_string.plist_get(l_plist, 'status'));
dbms_output.put_line('exists: ' || case when apex_string.plist_exists(l_plist, 'carrier') then 'carrier' end);
dbms_output.put_line('key of UPS: ' || apex_string.plist_get_key(l_plist, 'UPS'));
apex_string.plist_delete(l_plist, 'carrier');
dbms_output.put_line('json: ' || apex_string.plist_to_json_clob(l_plist));
end;
/Output:
status: DELIVERED
exists: carrier
key of UPS: carrier
json: {"status":"DELIVERED","note":"left at door"}Property lists are handy for passing a handful of named options between procedures, or for building a small JSON payload without APEX_JSON.
Large Text and Search Terms
NEXT_CHUNK
Reads a CLOB in pieces of up to 32,767 characters, for example to write it with htp.prn or pass it to an API that only takes VARCHAR2. It returns false after the last piece. p_offset starts as null and moves forward with each call.
Syntax:
apex_string.next_chunk(p_str in clob, p_chunk out nocopy varchar2, p_offset in out nocopy integer,
p_amount in integer default 8191) return booleanExample:
declare
l_clob clob := rpad('x', 20000, 'x');
l_chunk varchar2(8191);
l_offset integer; -- null: start at the beginning
begin
while apex_string.next_chunk(p_str => l_clob, p_chunk => l_chunk, p_offset => l_offset, p_amount => 8000) loop
dbms_output.put_line('chunk of ' || length(l_chunk) || ', next offset ' || l_offset);
end loop;
end;
/Output:
chunk of 8000, next offset 8001 chunk of 8000, next offset 16001 chunk of 4000, next offset 20001
This replaces the usual hand-written DBMS_LOB.SUBSTR loop. Printing a CLOB on a page this way is also shown in displaying CLOB contents in Oracle APEX.
GET_SEARCHABLE_PHRASES
Returns the words and phrases of up to p_max_words words from some texts, in lower case and without the language's stop words: the terms a search index or tag cloud needs.
Syntax:
apex_string.get_searchable_phrases(p_strings in apex_t_varchar2, p_max_words in pls_integer default 3,
p_language in apex_t_varchar2 default 'en') return apex_t_varchar2Example:
select column_value as phrase
from table(apex_string.get_searchable_phrases(
p_strings => apex_t_varchar2('Trailblazer 2-Person Tent', 'Ultralight tent for two hikers'),
p_max_words => 2));Output:
PHRASE -------------------- trailblazer trailblazer 2-person 2-person 2-person tent tent ultralight ultralight tent two two hikers hikers
The stop word "for" was dropped and never appears inside a phrase, and every term came back in lower case.
Finding Things in Text: APEX_STRING_UTIL
The Find Functions
These functions find email addresses, the sender and subject of a mail, links, hashtags, identifiers with a prefix, and phrases from a list inside a text. PHRASE_EXISTS tells whether a single phrase occurs. They return apex_t_varchar2 lists or single values.
| Function | Returns |
|---|---|
| FIND_EMAIL_ADDRESSES(p_string) | The email addresses. |
| FIND_EMAIL_FROM(p_string), FIND_EMAIL_SUBJECT(p_string) | The sender and subject from a mail's headers. |
| FIND_LINKS(p_string, p_https_only) | The URLs. |
| FIND_TAGS(p_string, p_prefix default '#', p_exclude_numeric default true) | The tags, in upper case. |
| FIND_IDENTIFIERS(p_string, p_prefix) | Identifiers that start with a prefix, such as order and bug numbers. |
| FIND_PHRASES(p_phrases, p_string) | Which of the given phrases occur. |
| PHRASE_EXISTS(p_phrase, p_string) | Whether the phrase occurs, ignoring case. |
Example:
declare
l_mail varchar2(4000) := 'From: Linda Kim <linda.kim@example.com>' || chr(10)
|| 'Subject: Order ORD-12280 cancelled' || chr(10)
|| 'Please refund to billing@example.com. See https://orbit-outfitters.example/orders/12280 #refund #urgent 2026';
begin
dbms_output.put_line('emails: ' || apex_string.join(apex_string_util.find_email_addresses(l_mail), ', '));
dbms_output.put_line('from: ' || apex_string_util.find_email_from(l_mail));
dbms_output.put_line('subject: ' || apex_string_util.find_email_subject(l_mail));
dbms_output.put_line('links: ' || apex_string.join(apex_string_util.find_links(l_mail), ', '));
dbms_output.put_line('tags: ' || apex_string.join(apex_string_util.find_tags(l_mail), ', '));
dbms_output.put_line('identifiers: ' || apex_string.join(apex_string_util.find_identifiers(l_mail, 'ORD'), ', '));
dbms_output.put_line('phrases: ' || apex_string.join(apex_string_util.find_phrases(apex_t_varchar2('refund', 'exchange'), l_mail), ', '));
dbms_output.put_line('exists: ' || case when apex_string_util.phrase_exists('order ord-12280', l_mail) then 'yes' else 'no' end);
end;
/Output:
emails: linda.kim@example.com, billing@example.com. from: linda.kim@example.com subject: Order ORD-12280 cancelled links: https://orbit-outfitters.example/orders/12280 tags: #REFUND, #URGENT identifiers: ORD-12280 phrases: refund exists: yes
Two quirks in 26.1. FIND_EMAIL_ADDRESSES keeps the period that ends a sentence, as in billing@example.com., so trim a trailing period before using an address. And a FIND_IDENTIFIERS prefix that ends with a dash doubles it: ORD- would look for ORD--12280, even though the documentation shows ORA- finding ORA-02291. Pass the prefix without the dash, as the example does with ORD.
These functions are a quick way to route incoming emails, for example by extracting the order number from a customer's message and attaching the mail to that order.
GET_SLUG, GET_DOMAIN, GET_FILE_EXTENSION, TO_DISPLAY_FILESIZE, and REPLACE_WHITESPACE
GET_SLUG turns a text into a URL-friendly slug, optionally with a hash for uniqueness. GET_DOMAIN returns a URL's host, GET_FILE_EXTENSION a file name's extension in lower case, and TO_DISPLAY_FILESIZE a size in bytes as a readable value such as 1.5MB. REPLACE_WHITESPACE replaces spaces and punctuation with a separator, for matching words regardless of punctuation.
Example:
begin
dbms_output.put_line('slug: ' || apex_string_util.get_slug('Trailblazer 2-Person Tent (Olive)!'));
dbms_output.put_line('with hash: ' || apex_string_util.get_slug('Trailblazer 2-Person Tent', p_hash_length => 6));
dbms_output.put_line('domain: ' || apex_string_util.get_domain('https://shop.orbit-outfitters.example/tents?id=7'));
dbms_output.put_line('extension: ' || apex_string_util.get_file_extension('price-list.2026.XLSX'));
dbms_output.put_line('file sizes: ' || apex_string_util.to_display_filesize(950) || ', '
|| apex_string_util.to_display_filesize(1536000) || ', ' || apex_string_util.to_display_filesize(3221225472));
dbms_output.put_line('whitespace: ' || apex_string_util.replace_whitespace('Order ORD-12283' || chr(9) || 'shipped'));
end;
/Output:
slug: trailblazer-2-person-tent-olive with hash: trailblazer-2-person-tent-966427 domain: shop.orbit-outfitters.example extension: xlsx file sizes: 950 bytes, 1.5MB, 3.0GB whitespace: |order|ord|12283|shipped|
TO_DISPLAY_FILESIZE is ideal for a file list built from uploaded BLOBs, as in uploading files in Oracle APEX.
DIFF
Compares two lists of lines and returns the differences in unified diff format, with p_context lines of context around each change.
Syntax:
apex_string_util.diff(p_left in apex_t_varchar2, p_right in apex_t_varchar2, p_context in pls_integer default 3) return apex_t_varchar2
Example:
select column_value as line
from table(apex_string_util.diff(
p_left => apex_t_varchar2('Tents', 'Stoves', 'Lanterns', 'Tarps'),
p_right => apex_t_varchar2('Tents', 'Stoves', 'Headlamps', 'Tarps'),
p_context => 1));Output:
LINE ---------- Tents @@ 3,2 @@ -Lanterns @@ 3,3 @@ +Headlamps
Removed lines start with a minus and added lines with a plus, which makes DIFF useful for showing what changed between two versions of a note or a configuration.
Conclusion
APEX_STRING covers the string work APEX code does all the time: FORMAT fills placeholders safely and readably, SPLIT and SPLIT_NUMBERS turn delimited values into rows, JOIN and its CLOB variants put them back together, PUSH, INDEX_OF, and GREP manage lists, property lists act as small maps, NEXT_CHUNK reads large CLOBs, and GET_SEARCHABLE_PHRASES extracts search terms. APEX_STRING_UTIL finds emails, links, tags, and identifiers in text and produces slugs, readable file sizes, and diffs. Remember to pass a separator to SPLIT, match whole values with GREP, and leave the dash off FIND_IDENTIFIERS prefixes in 26.1.
