Posts Tagged ‘combine columns’

Joining columns/ concatenate strings in Oracle PLSQL/ MySQL

oracle-concat

Joining columns and results together is really easy in Oracle or MySQL, in both there is the same function – CONCAT().

However you should be aware that there are differences between the databases on how this function works. In MySQL the CONCAT() function works by joining any number of items specified in the function – I don’t think that there is a limit? But with Oracle it seems that you can only join two strings at a time using this function.

However Oracle offers an alternative for concatenation which also allows us to specify additional characters between the strings that we wish to join. To do this we use ||”||, so to add a comma we do column1||’,’||column2||’,’||column3.

I believe that in MsSQL the concatenation function just uses a plus sign: column1 + column2 + string1 etc…

Generally we use these functions as part of our SELECT statement so for instance if I have a table that stores First Name and Last Name as two separate columns and then I need an export which needs them combined into one field, I can do:

SELECT CONCAT(first_name, last_name) As "Name" FROM [TABLE] WHERE ...

To do multiple CONCAT() in Oracle you could probably do something retarded like:

SELECT CONCAT(CONCAT(first_name, ','), last_name) As "Name" FROM [TABLE] WHERE ...

But it’s just easier to do:

SELECT 'Mr'||first_name||','||last_name As "Name" FROM [TABLE] WHERE ...

In MySQL we use the same CONCAT() function which is just the same, except I’m not limited by the number of columns/ strings I can join:

SELECT CONCAT(first_name, last_name) As "Name" FROM [TABLE] WHERE ...

And if we want to add in strings or commas in MySQL we then do:

SELECT CONCAT('Mr', first_name, ',', last_name, 'some text') As "Name" FROM [TABLE] WHERE ...

Typically I find the concatenation function is really useful in Oracle when I want to export a CSV file with out quotes around each field, I just concatenate all columns together – it also means that any quotes in the field records aren’t lost either like in my example here:

Spool a CSV file from Oracle/ SQL*Plus without quotes