I have 4 columns which I need to concat using , as seperator
I tried below query
Select concat(concat(concat(concat(name,','),age,','),surname,','),city,',') from student
But got the error as The number of argumemts specified for concat is invalid
Can anyone help me what went wrong
Try instead:
select coalesce(name,'')||','||coalesce(age,'')||','||coalesce(surname,'')||','||coalesce(city,'')||',' from student
The operator || is the concatenation operator, and is usually more readable.
The coalesce() is only needed when the column can contain NULL , because if any of the column do contain NULL then the result of the whole expression will be NULL, so replace them by empty-string or other character of your choice with the coalesce() function.
To use the concat function, which takes two arguments, you can use the below, which you will still need to protect with coalesce() if any of the columns are nullable:
select concat(concat(concat(concat(concat(concat( concat(name,','), age),','),surname),','),city),',') from student;
If the datatype of the columns is CHAR() - which is fixed width, then any leading or trailing spaces will appear in the output. If you do not want these spaces then you must use the TRIM() function on each of these columns. You could avoid this if you had a better design for your table columns , i.e to use VARCHAR as the datatype of the columns that can have variable lenght data because in this case the trailing spaces are automatically removed by the database engine.
