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 (string1IN VARCHAR2,string2IN 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) --> NULLI 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.
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Read now
Unlock full access