HugoLim.com

In Oracle world, we have two ways of building up a string of value. You may use the CONCAT function or the double pipes symbol ||

Table: student

CONCAT

Example: SELECT CONCAT(first_name, last_name) FROM student WHERE id = 1

Result: MichaelOwen

||

Example: SELECT first_name || last_name FROM student WHERE id =1

Result: MichaelOwen

There isn’t much difference in term of typing effort but things would start to look ugly and tedious when you try to merge 3 columns using CONCAT function.

CONCAT

Example: SELECT CONCAT( CONCAT(first_name, last_name), middle_name) FROM student WHERE id = 1

Result: MichaelOwenJack

||

Example: SELECT first_name || last_name || middle_name FROM student WHERE id =1

Result: MichaelOwenJack

Extra info:

Using + sign in Oracle is a big no no. You can only use + when you need to sum up two columns.

You can only pass in two arguments to use the CONCAT function in Oracle unlike MYSql where you can pass in as many as you want to. For Oracle, I recommend you to use || instead for greater flexibility and easing your pain. :)

Below are quoted from infogoal.com

MySQL uses the CONCAT() function

Oracle uses the CONCAT() function and the || operator

SQL Server uses the + operator

Happy DB-ing!

Popularity: 1% [?]

Related posts:

1. Oracle SQL – Return value regardless of record existance
2. Radio Math Game
3. Lesson 101 for SQL Query in VB

Related posts brought to you by Yet Another Related Posts Plugin.