Why nvl doesn't work inside concat function in oracle?

I’m using a query to select all columns of a table and separate them by a comma(‘,’) but in case if value of a column is null then I’ll use string ‘null’ in place of a value.

The query I am trying is –

SELECT CONCAT(NVL(ID,'null'),',',NVL(NAME,'null'),',',NVL(ROLL_NO,'null')) 
FROM DUAL
Expected result-
1,john,123
2,josh,null

I intend to run this query on a spark temporary table. But before that I tried running it on sql developer.
But I’m getting ORA-00909 : invalid number of arguments error. I can’t find where I’m going wrong here.

Extra question (not necessary to answer) : Is there a way to concatenate all columns by not writing columns manually? I know there is a function concat_ws in spark.sql and oracle, where we can use a delimiter but it also neglects null value instead of replacing them with ‘null’ string but again I’ve to write all columns manually even in concat_ws.

>Solution :

Oracle’s CONCAT function accepts only 2 arguments.

But there is the concat operator, ||, which does what you want:

SELECT NVL(ID,'null') || ',' || NVL(NAME,'null') || ',' || NVL(ROLL_NO,'null') 
FROM DUAL

Leave a Reply