In Oracle, we can concatenate the string and numbers using two pipes "||" and by using CONCAT function. The CONCAT function takes only 2 arguments, means it can only concatenate 2 given strings or numbers. Using two PIPES you can concatenate any number of strings and numbers.
Oracle Concatenate String With Number Examples
1. Concatenate String and Numbers Using PIPES
In the following example, it will concatenate multiple strings and numbers using two PIPES.
SET SERVEROUTPUT ON;
BEGIN
dbms_output.put_line('abc '|| 'xyz ' || 123 || ' $');
END;
/Output:
abc xyz 123 $ PL/SQL procedure successfully completed.
2. Concatenate String with Calculated Number Values
In the following example, it will concatenate the string with numbers using arithmetic operators for calculated values. Please note that for calculated values you should use parenthesis else it will give the Numeric or Value error.
SET SERVEROUTPUT ON;
BEGIN
dbms_output.put_line('abc'|| 'xyz' || (5 + 4)|| '$');
END;
/Output:
abcxyz9$ PL/SQL procedure successfully completed.
3. Using CONCAT Function
In the following example, it will concatenate the two given arguments. In the CONCAT function, there is no need to use parenthesis for calculated values.
SET SERVEROUTPUT ON;
BEGIN
dbms_output.put_line(concat('abc', 3 + 6));
END;
/Output:
abc9 PL/SQL procedure successfully completed.
See also:
