Oracle PL/SQL Programming, Third Edition

Name

CONCAT

Synopsis

The CONCAT function concatenates by taking two VARCHAR2 strings and returning them appended together in the order specified. The specification of the CONCAT function is:

FUNCTION CONCAT (string1 IN VARCHAR2, string2 IN VARCHAR2)
                RETURN VARCHAR2

CONCAT always appends string2 to the end of string1. If either string is NULL, CONCAT returns the non-NULL argument all by its lonesome. If both strings are NULL, CONCAT returns NULL. Here are some examples of uses of CONCAT (where --> means that the function returns the value shown):

CONCAT ('abc', 'defg') --> 'abcdefg'
CONCAT (NULL, 'def') --> 'def'
CONCAT ('ab', NULL) --> 'ab'
CONCAT (NULL, NULL) --> NULL

I have a confession to make about CONCAT: I have never used it in all my years of PL/SQL coding. In fact, I never even noticed it was available until I did the research for this book. How can this be? I certainly have performed many acts of concatenation in my time. However, PL/SQL (and the Oracle RDBMS) offers a second concatenation operator—the double vertical bars (||). For example:

DECLARE
   x VARCHAR2(100);
BEGIN 
   x := 'abc' || 'def' || 'ghi';
   DBMS_OUTPUT.PUT_LINE(x);
END;

The output is:

abcdefghi

To perform the identical concatenation using CONCAT, we’d need to nest one call to CONCAT inside another:

x := CONCAT(CONCAT('abc','def'),'ghi');

You can see that the || operator is not only much easier to use than CONCAT, but results in much more readable code.