How do I remove CHR(0) from strings when using LISTAGG in Oracle DB?

I am trying to query an oracle database and get a list of names in a single cell of the result.

I have a query like the following which produces the names in separate rows:

SELECT (lastname || ', ' || firstname) as fullname
FROM users
WHERE {some condition}

-- result --    
    fullname
Anderson, Alex
Baker, Bob
Clark, Carl

However, when I try and use LISTAGG to concatenate those rows I get the following error:

SELECT LISTAGG(fullname, '; ') WITHIN GROUP (ORDER BY fullname) as instructors
FROM
(
    SELECT (lastname || ', ' || firstname) as fullname
    FROM users
    WHERE {some condition}
)

-- desired result --
    instructors
Anderson, Alex; Baker, Bob; Clark, Carl

-- actual result --
Error Type: System.Xml.XmlException
Error Message: hexadecimal value 0x00, is an invalid character. Line 1, position 32.

Note that if I use LISTAGG on a string made from concatenating INT values, the query works as expected:

SELECT LISTAGG(fullname, '; ') WITHIN GROUP (ORDER BY fullname) as instructors
FROM
(
    SELECT (pk1 || ', ' || pk1) as fullname    -- pk1 is the users primary key
    FROM users
    WHERE {some condition}
)

-- result --
    instructors
01, 01; 02, 02; 03, 03

I suspect the problem is that the firstname and lastname fields are null-terminated and LISTAGG is not properly removing the null characters from the result. I’m not sure how I can remove the null characters from the result of the inner query though.

Note that this is a client’s database and I am not allowed to modify its contents. I need to be able to read the fields in their current format and produce the desired output.

Answer

I had the same problem. The solution for me was:

SELECT replace(LISTAGG(fullname, '; ') WITHIN GROUP (ORDER BY fullname), chr(0)) as instructors

Advertisement

Answer