How can I combine two volumn in oracle sql without using CASE？
I have to combine First name and LastName in the same row.
I have a table as below：
FirstName LastName Ken Chan John Ng Joe Lam
The data type of them 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 have 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.
Concatenation Operator that is
||. It concatenates character strings and results in another character string.
, comma optional in case if any of the LastName or firstName field is empty or null, use
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: