Press "Enter" to skip to content

Traditional Searching, Extracting, and Replacing String in Oracle PL/SQL

Traditional Searching, Extracting, and Replacing String in Oracle PL/SQL

Frequently, you’ll find yourself wanting to search a string for a bit of text. Starting with
Oracle Database 10g, you can use regular expressions for these textual manipulations;
see the section later in this chapter on regular expressions for the full details. If you’re
not yet using Oracle Database 10g or later, you can use an approach that is backwardcompatible
to older database versions. The INSTR function returns the character position
of a substring within a larger string. The following code finds the locations of all
the commas in a list of names:
 
DECLARE
names VARCHAR2(60) := ‘Anna,Matt,Joe,Nathan,Andrew,Aaron,Jeff’;
comma_location NUMBER := 0;
BEGIN
LOOP
comma_location := INSTR(names,’,’,comma_location+1);
EXIT WHEN comma_location = 0;
DBMS_OUTPUT.PUT_LINE(comma_location);
END LOOP;
END;

The output is:

5
10
14
21
28
34

The first argument to INSTR is the string to search. The second is the substring to look
for, in this case a comma. The third argument specifies the character position at which
to begin looking. After each comma is found, the loop begins looking again one character
further down the string. When no match is found, INSTR returns zero, and the
loop ends.

Having found the location of some text in a string, a natural next step is to extract it. I
don’t care about those commas. Let’s extract the names instead. For that, I’ll use the
SUBSTR function:

DECLARE
names VARCHAR2(60) := ‘Anna,Matt,Joe,Nathan,Andrew,Aaron,Jeff’;
names_adjusted VARCHAR2(61);
comma_location NUMBER := 0;
prev_location NUMBER := 0;
BEGIN
–Stick a comma after the final name
names_adjusted := names || ‘,’;
LOOP
comma_location := INSTR(names_adjusted,’,’,comma_location+1);
EXIT WHEN comma_location = 0;
DBMS_OUTPUT.PUT_LINE(
SUBSTR(names_adjusted,
prev_location+1,
comma_location-prev_location-1));
prev_location := comma_location;
END LOOP;
END;

The list of names that I get is:

Anna
Matt
Joe
Nathan
Andrew
Aaron
Jeff

The keys to the preceding bit of code are twofold. First, a comma is appended to the
end of the string to make the loop’s logic easier to write. Every name in names_adjusted
is followed by a comma. That simplifies life. Then, each time the loop iterates to
DBMS_OUTPUT.PUT_LINE, the two variables named prev_location and
comma_location point to the character positions on either side of the name to print.

It’s then just a matter of some simple math and the SUBSTR function. Three arguments
are passed:
names_adjusted
The string from which to extract a name.
prev_location+1
The character position of the first letter in the name. Remember that prev_location
will point to just before the name to display, usually to a comma preceding the
name. That’s why I add 1 to the value.
comma_location-prev_location-1
The number of characters to extract. I subtract the extra 1 to avoid displaying the
trailing comma.

All this searching and extracting is fairly tedious. Sometimes I can reduce the complexity
of my code by cleverly using some of the built-in functions. Let’s try the
REPLACE function to swap those commas with newlines:

DECLARE
names VARCHAR2(60) := ‘Anna,Matt,Joe,Nathan,Andrew,Aaron,Jeff’;
BEGIN
DBMS_OUTPUT.PUT_LINE(
REPLACE(names, ‘,’, chr(10))
);
END;

And the output is (!):

Anna
Matt
Joe
Nathan
Andrew
Aaron
Jeff

By using REPLACE I was able to avoid all that looping. I got the same results with code
that is more simple and elegant. Of course, you won’t always be able to avoid loop
processing by using REPLACE, but it’s good to know about alternative algorithms.
With programming, there are always several ways to get the results you want!