Oracle SQL - combine two columns-CodePudding

How can I combine two columns in Oracle SQL without using CASE?

I have to combine FirstName and LastName in the same row.

I have a table as below：

FirstName     LastName
-----------------------
Ken           Chan
John          Ng
Joe           Lam

The data type of these columns is VARCHAR2.

I try to apply the code as follow

SELECT 
    CONCAT(LastName, ‘,’, FirstName) AS FullName 
FROM
    LIST 
ORDER BY 
    Place;

SELECT 
    LastName, ‘,’, FirstName AS FullName 
FROM
    LIST 
ORDER BY
    Place;

But both of them result in the the same error

ORA-00909: invalid number of arguments.

May I also ask how can I not adding the ‘,’ while there is missing LastName or FirstName?

Such as not adding ‘,’ when there is only having LastName Chan. The FullName only display as Chan but not ,Chan.

Thanks a lot for helping me.

CodePudding user response：

Use Concatenation Operator that is ||. It concatenates character strings and results in another character string.
To make , comma optional in case if any of the LastName or firstName field is empty or null, use CASE statement.

Solution for your problem:

SELECT LastName || (CASE WHEN LastName IS NOT NULL AND FirstName IS NOT NULL THEN ',' END) || FirstName as FullName
FROM LIST
ORDER BY place;

Working Example: db<>fiddle Link

For more info on Concatenation Operator follow below link to official docs:

https://docs.oracle.com/cd/B19306_01/server.102/b14200/operators003.htm

CodePudding user response：

Oracle's implementation of the concat() function only permits 2 parameters, and you have tried 3, that is why you got the error message.

It is simpler, in Oracle, to use the more traditional double pipe operator to form a concatenated string.

SELECT LastName || ‘,’ || FirstName as FullName 
from LIST 
order by Place;