Database Reference
In-Depth Information
Figure 14.4
REGEXP_SUBSTR
and Finding Song
Titles with Three
or More Words.
with three words returns the middle word; a string with four words returns
the middle two words; and strings with either one or two words are not
returned at all. In other words, the first and last words are removed as long
as there are three words or more to the song title. The result is shown in
Figure 14.4.
SELECT TITLE, VAL FROM(SELECT TITLE
, REGEXP_SUBSTR(title, ' [^,]+ ', 2) AS VAL FROM SONG
) WHERE VAL IS NOT NULL;
The next example uses the REGEXP_REPLACE function to replace all
occurrences of the capital letter A in song titles with a string of three aster-
isks. The result is shown in Figure 14.5.
SELECT TITLE, VAL FROM(SELECT TITLE
, REGEXP_REPLACE(title, 'A(/*)', '***\1') AS VAL
FROM SONG
) WHERE VAL LIKE '%***%';
