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.

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

apex_string.format

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'));
  -- The code below produces the same result but I find
  --  the above code easier to read and maintain.
  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.