Listagg Function Inserts Spaces in Result/Output (Doc ID 2099011.1)

Last updated on AUGUST 09, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to 12.1]
Linux x86-64
Oracle Solaris on SPARC (64-bit)
***Checked for relevance on 09-Aug-2017***

Symptoms

The following query returns 16 rows with no spaces

SELECT DISTINCT ''''
  ||coledg
  ||'''' coledg
FROM proddta.f4105,
  proddta.f0006
WHERE comcu = mcmcu
AND mcco IN ('03061','03072','03011','03063','08408','08270')
AND mcstyl ='BP';

However, when an attempt is made to use LISTAGG to concatenate (link) these rows into one, unexpected results are obtained:

SELECT listagg(coledg,',') WITHIN GROUP (
ORDER BY coledg) namelist
FROM
(
SQL Query
);


NAMELIST
--------------------------------------------------------------------------------
 ' 0 1 ', ' 0 2 ', ' 0 7 ', ' 0 8 ', ' 9 8 ', ' I C ', ' I N ', ' S 0 ', ' S 1 '
, ' S 2 ', ' S 3 ', ' S 7 ', ' S 8 ', ' S 9 ', ' T P ', ' U 7 '


As can be seen, there is a white space inserted at the start of the result string and between each character in the result set (except before the commas).

Example of expected result: '01','02','07'....,'U7' with no spaces - as per the data returned from the nested subquery.

Changes

 NA

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms