11.6. String Concatenation

The concatenation operator is:

||

This operator concatenates strings, but you can concatenate other types. PL/SQL does implicit conversion, such as converting a number and date type variable to a string. This enables statements to concatenate various types, but only if the concatenated item converts to a string.

DECLARE
    professor_name VARCHAR2(100) := 'Professor Smith';
    hire_date      DATE          := SYSDATE;
    dalary         NUMBER(7,2)   := 10100.50;
    str            VARCHAR2(2000);
BEGIN
    str := professor_name|| ' was hired on '||
           hire_date ||' with a salary of '||
           salary||' per month. ';
    dbms_output.put_line(str);
END;

You precede each single quote that is part of the string with a quote. For example, your string needs to include a single quote because the final text must be this next line:

A quote like ' is needed.

You produce this output by preceding the quote with a quote. This identifies the character as a quote in the string rather than the end of the string.

DECLARE
    str varchar2(100);
BEGIN
    str := 'A quote like '' is needed.';
    dbms_output.put_line(str);
END;

When quotes must begin or end a string, you still have two quotes. You also have the string-terminating quote. You want the output to be:

'A quote like ' is needed.'

You form this string with the following:

DECLARE
    str varchar2(100);
BEGIN
    str := '''A quote like '' is needed.''';
    dbms_output.put_line(str);
END;

The SQL*Plus environment uses “&” for parameter notation. When you write PL/SQL in a SQL*Plus environment, the use of this symbol and the text that follows it will be interpreted as a SQL*Plus parameter. This is only an issue in SQL*Plus. To use & in a string, replace it with the CHR function. The CHR function is a built-in function that returns the ASCII character from an equivalent ASCII number. The ASCII integer for & is 38. Refer to Section 11.14, “Miscellaneous String Functions,” for a description of CHR and ASCII. If our output is:

'A quote like ' and a & is needed.'

You can display this with the following.

DECLARE
    str varchar2(100);
    ch  varchar2(1) := CHR(38);
BEGIN
    str := '''A quote like '' and a '||ch||' is needed.''';
    dbms_output.put_line(str);
END;