Introduction
I recently had the pleasure of joining Steven Feuerstein on his show Feurtips.
In addition to sharing an essential fact about Wales, I presented more than a dozen examples of the APEX_STRING and APEX_STRING_UTIL PL/SQL APIs.
🇨ğŸ‡
Steven described these APIs as the Swiss Army knife of String Utilities. I couldn't agree more. These APIs and many other APIs that come with APEX make it worth installing APEX even if (heaven forbid) you don't plan on using APEX.
In this post, I will review the examples I presented on the show.
apex_string.get_initials
This API allows you to extract initials for a given string. This can be useful when displaying a shortened username in an avatar icon or a chat window.
SELECT apex_string.get_initials(p_str => 'John Doe', p_cnt => 2) initials
FROM dual UNION ALL
SELECT apex_string.get_initials(p_str => 'John doe', p_cnt => 3) initials
FROM dual UNION ALL
SELECT apex_string.get_initials(p_str => 'John Frank Doe', p_cnt => 2) initials
FROM dual UNION ALL
SELECT apex_string.get_initials(p_str => 'John Frank Doe', p_cnt => 3) initials
FROM dual;
INITIALS
JD
JD
JF
JFD
apex_string_util.to_display_filesize
This API displays a user-readable file size. It dynamically changes the unit of measure (GB, MB, KB, etc.) based on the number of bytes provided. This is useful when displaying a list of SharePoint files and their sizes.
SELECT apex_string_util.to_display_filesize(p_size_in_bytes => 1312312312) file_size_dsp
FROM dual UNION ALL
SELECT apex_string_util.to_display_filesize(p_size_in_bytes => 13123123) file_size_dsp
FROM dual UNION ALL
SELECT apex_string_util.to_display_filesize(p_size_in_bytes => 131231) file_size_dsp
FROM dual UNION ALL
SELECT apex_string_util.to_display_filesize(p_size_in_bytes => 1312) file_size_dsp
FROM dual;
FILE_SIZE_DSP
1.2GB
12.5MB
128.2KB
1,312 bytes
apex_string_util.get_domain
This API returns the domain portion of an email address or URL. I have used this when implementing Social Sign-on with Office 365. I use it to determine if the logged-in user has an email address on an allowlist of email domains allowed to use an Application.
SELECT apex_string_util.get_domain (p_string => 'jon@cloudnueva.com') domain_name
FROM dual UNION ALL
SELECT apex_string_util.get_domain (p_string => 'https://www.cloudnueva.com/djddjdhjdhd') domain_name
FROM dual;
DOMAIN_NAME
cloudnueva.com
cloudnueva.com
I primarily use this API to format log messages. It allows me to write the message in plain English and inject variables into the message. I find this easier to read (and maintain) than concatenating values.
BEGIN
dbms_output.put_line (apex_string.format('Customer Number [%s], is invalid. Message [%s]',
'2226S', 'Customer Disabled'));
dbms_output.put_line ('Customer Number [' || '2226S' ||
'], is invalid. Message [' || 'Customer Disabled' ||']');
END;
/
Customer Number [2226S], is invalid. Message [Customer Disabled]
Customer Number [2226S], is invalid. Message [Customer Disabled]
apex_string.split
Plus: split_numbers and split_clobs
This API is the bread and butter of APEX Developers. It is beneficial when implementing APEX Popup LOV or Combo Boxes (new in APEX 23.2) that allow multi-select. They allow you to take a delimited string and turn it into a table.
For example, we can take the following list of item IDs returned from a Popup LOV: 233:528:971:1868:2232:3067:4656 and turn them into a table as follows:
SELECT column_value
FROM TABLE(apex_string.split_numbers(p_str => '233:528:971:1868:2232:3067:4656',
p_sep => ':'));
COLUMN_VALUE
233
528
971
1868
2232
3067
4656
This allows us to use the list in our SQL like this:
SELECT *
FROM mdm_items
WHERE item_id MEMBER OF
(SELECT apex_string.split_numbers(p_str => :P10_ITEM_IDS,
p_sep => ':') FROM dual);
apex_string_util.find...
The 'find' series of APIs helps you to find particular objects within a string.
For example, I use apex_string_util.find_links to find URLs within an APEX Rich Text field. This may be useful if you need to check the links a user entered in a Rich Text field before allowing it to be saved.
SELECT column_value
FROM apex_string_util.find_links
(p_string => '<p>Click <a href="https://www.google.com">here</a> or Click <a href="https://www.abc.com">here</a></p>');
COLUMN_VALUE
https://www.google.com
https://www.abc.com
Similarly, apex_string_util.find_email_addresses extracts an array of email addresses from a string:
SELECT column_value
FROM apex_string_util.find_email_addresses
(p_string => 'Email Jon at jon@cloudnueva.com and Bill at bill@abc.com tomorrow.');
COLUMN_VALUE
jon@cloudnueva.com
bill@abc.com
Finally, apex_string_util.find_tags extracts a list of Tags from a string:
SELECT column_value
FROM apex_string_util.find_tags (p_string => 'We love #orclAPEX# and #orclORDS!',
p_prefix => '#');
COLUMN_VALUE
#ORCLAPEX
#ORCLORDS
apex_string.push
This API allows you to maintain a simple array of values. This could be useful if you need to capture values during a long-running PL/SQL process so you can return to them later. I have provided a detailed example in a previous post Working with Arrays in APEX and PL/SQL.
apex_string.plist...
The plist series of APIs allow you to maintain key-value pairs in a PL/SQL array. I have also provided a detailed example of this API in the previous post, Working with Arrays in APEX and PL/SQL.
apex_string_util.get_slug
This API removes spaces, punctuation, and special characters from a string. It returns a maximum of 255 characters. A Slug is the unique identifying part of a web address, typically at the end of the URL. I have also used this API to generate a unique string based on an input string.
select apex_string_util.get_slug (p_string => 'Jon Dixon', p_hash_length => 10) from dual
union all
select apex_string_util.get_slug (p_string => 'Jon Dixon', p_hash_length => 10) from dual
union all
select apex_string_util.get_slug (p_string => 'This is a sentence. Some random characters ~!@#$%^&*()-=') from dual;
COLUMN_VALUE
jon-dixon-0168304377
jon-dixon-2968790994
this-is-a-sentence-some-random-characters
apex_string_util.replace_whitespace
Finally, the apex_string_util.replace_whitespace API allows you to remove whitespace from a string and replace it with another character. Note: this API does not remove all special characters from the original string.
select apex_string_util.replace_whitespace
(p_string => 'This is a sentence. Some random characters ~!@#$%^&*()-=',
p_whitespace_character => '+') from dual;
REPLACED_WHITESPACE
+this+is+a+sentence+some+random+characters+~+@+$%^&*+=+
Conclusion
It is always worth reminding ourselves of the APIs (PL/SQL and JavaScript) that APEX has to offer. Even if it would only take ten lines of code for you to write a utility function, it is better to use an APEX function and let the APEX development team worry about maintaining and enhancing it.
📌
I will leave you with a URL to pin to your Bookmarks bar. This URL will always take you to the documentation for the PL/SQL APIs for the current version of APEX:
https://apex.oracle.com/api 
