Oracle Listagg Overflow truncate on the begining

Question

I have a question about the function listagg in Oracle I would like when the listagg exceeds the limit concatenation of 4000 characters to truncate the extra characters.

I have found that there is the clause ON OVERFLOW TRUNCATE '...', but is it possible to add the '...' at the start of the concatenation and keep only the 4000 characters ?

Thanks in advance

Answer 1

Check if the string is going to overflow and, if so, remove the overflow characters from the end and prepend them to the string:

SELECT CASE
       WHEN SUM(LENGTH(value) + LENGTH(',')) - LENGTH(',') > 4000
       THEN '...'
            || SUBSTR(
                 LISTAGG(value, ',' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT)
                   WITHIN GROUP (ORDER BY value),
                 1,
                 3997
               )
       ELSE LISTAGG(value, ',' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT)
              WITHIN GROUP (ORDER BY value)
       END AS list
FROM   table_name

fiddle

Answer 2

What you could do is appending the text you want at the start also at the end of the '...' and trim that text again before concatenating it with the start text.

So: 'starttext:' || regexp_replace(listagg .... ON OVERFLOW TRUNCATE '... and more. starttext') WITHIN GROUP (ORDER BY a) ,'(.*)( starttext)$','\1')