Oracle PL/SQL Programming, 5th Edition

Working with Strings

Working with strings is largely a matter of manipulating those strings using Oracle’s rich library of built-in string functions. To that end, I recommend that you become broadly familiar with the functions Oracle has to offer. In the subsections that follow, I’ll begin by showing you how to write string constants, and then introduce you to the string manipulation functions that I have come to find most important in my own work.

Specifying String Constants

One way to get strings into your PL/SQL programs is to issue a SELECT statement that returns character string values. Another way is to place string constants directly into your code. You write such constants by enclosing them within single quotes:

'Brighten the corner where you are.'

If you want to embed a single quote within a string constant, you can do so by typing the single quote twice:

'Aren''t you glad you''re learning PL/SQL with O''Reilly?'

If your program will be dealing with strings that contain embedded single quote characters, a more elegant approach is to specify your own string delimiters. Do this using the q prefix (uppercase Q may also be specified). For example:

q'!Aren't you glad you're learning PL/SQL with O'Reilly?!'

or:

q'{Aren't you glad you're learning PL/SQL with O'Reilly?}'

When you use the q prefix, you still must enclose the entire string within single quotes. The character immediately following the first quotation mark—an exclamation point (!) in the first of my two examples—then becomes ...