Hey geeks,
This is another chapter of Operators. Concatenation Operator helps to concatenate two or more strings or columns. The output of concatenating two character strings is always a character string.
In Oracle database, two solid vertical bars (“||”) are used as Concatenation Operator. It is very simple to use. Have a look at the examples below.
Example-1:
Below query shows how we can concatenate two columns.
SELECT
first_name || ' ' || last_name AS CONCAT_EXAMPLE_1
FROM
employees
WHERE
ROWNUM < = 5;
| CONCAT_EXAMPLE_1 |
|---|
| Ellen Abel |
| Sundar Ande |
| Mozhe Atkinson |
| David Austin |
| Hermann Baer |
Example-2:
We can also concatenate any string value with a column.
SELECT
'First Name is ' || first_name AS CONCAT_EXAMPLE_2
FROM
employees
WHERE
ROWNUM < = 5;
| CONCAT_EXAMPLE_2 |
|---|
| First Name is Ellen |
| First Name is Sundar |
| First Name is Mozhe |
| First Name is David |
| First Name is Hermann |
We can use this operator to concatenate numbers as well. You should try yourself, GO AHEAD.
Important:
- If two CHAR data type strings are concatenated then the result will be of CHAR data type and is limited to 2000 characters.
- If either string is of data type VARCHAR2 then the result will be of VARCHAR2 data type and is limited to 4000 characters if the initialization parameter is set to MAX_STRING_SIZE = STANDARD. If the initialization parameter is set to MAX_STRING_SIZE = EXTENDED then the output limit is 32767 characters.
- If both the strings are of data type NUMBER, the result will be of VARCHAR2 data type.
If you have any questions/suggestions, please comment below or write me an email.
If you like this blog, do share with your friends and colleagues on your social media. For more updates join my Facebook group and do like my Facebook page.
Thank you,
Kapil Kumar
