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;