select * from table( apex_string.grep(
'This is a text with some mail adresses. The first one is a.a@oracle.com, we have ' ||
'noname@company.com and thesupport@firma.de',
'[A-Z0-9._%+-]*@[A-Z0-9.-]*\.[A-Z]*',
'i'
));
COLUMN_VALUE
--------------------------
a.a@oracle.com
noname@company.com
thesupport@firma.de

Application Express 5.1 has been released recently - and as
earlier versions, it contains new PL/SQL packages, which are also usable outside of an APEX context. In this blog posting
I'd like to introduce the
APEX_STRING package, which is very interesting for all SQL and PL/SQL developers.

APEX_STRING contains functions and procedures to work with - as the name indicates, strings. Let's start with the
SPLIT function, which is a String Tokenizer (which we need that all the time, right?).
SPLIT can be used in a SQL context ...

If you are interested only in the first few values of a larger string (or a larger CLOB), the
P_LIMIT argument gets important: The function will stop when the limit has been reached, thus saving
CPU cycles. The last element of the returned array will contain the rest of the input string.

SPLIT also supports the CLOB datatype - for cases, where CLOBs are to be tokenized. SPLIT_NUMBERS returns
an array of NUMBER elements.

Careful readers might have noticed that the above PL/SQL block contains another
APEX_STRING function: FORMAT. FORMAT allows building a string, containing fixed literals
and variable contents, by replacing placeholders. Code using APEX_STRING is much better readable
and maintainable than code using classic || concatenations.

As the following code example illustrates, you can use the %s and %{N} placeholders. FORMAT
then replaces the n-th %s with the value of the n-th p{N} function argument. Each p{N} argument
can also be referenced directly with %{N} i.e. %0 references the p0 argument, %1 will
reference p1 and so on. That syntax allows to use p{N} arguments more than once.

The PLIST_* procedures allow to maintain simple property lists, which consist of key-value pairs. These lists
are stored in an array of the WWV_FLOW_T_VARCHAR2 type and are maintained with PLIST_GET, PLIST_PUT and PLIST_DELETE.
The following sample code shows how it works.

PUSH is an easy way to add a single scalar value to an array; the array is treated like a stack. The following
PL/SQL block adds a few values with PUSH, then uses the SHUFFLE function to randomize element order and it finally
prints the array out.

For me, the coolest function within APEX_STRING is GREP, which extracts substrings based on regular expressions,
but which returns an array instead of a single string. Here's an example, which extracts all email addresses from a
string (the regular expression to match email addresses is a very simple one):

select * from table( apex_string.grep(
'This is a text with some mail adresses. The first one is a.a@oracle.com, we have ' ||
'noname@company.com and thesupport@firma.de',
'[A-Z0-9._%+-]*@[A-Z0-9.-]*\.[A-Z]*',
'i'
));
COLUMN_VALUE
--------------------------
a.a@oracle.com
noname@company.com
thesupport@firma.de

Having all matches in an array instead of a string makes post-processing a lot easier.

Finally, we'll show the JOIN function as the counterpart to SPLIT, which we introduced at the beginning of the posting. So we
came full circle.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.