CODEWITHSUNDEEP
sqloracle-database
Praveen Kesani
Praveen Kesani

Reputation: 178

How to make my result String 1000 characters long using LISTAGG

I want to limit my result string to 1000 characters when I use LISTAGG.

Here is my example query:

SELECT LISTAGG(EMPLOYEE_NAME,', ') WITHIN GROUP (ORDER BY EMPLOYEE_NAME) AS NAMES
FROM TB_EMPLOYEE;

If NAMES size is more than 1000 characters long, I just want only 1000 characters and ignore the remaining.

Upvotes: 0

Views: 1275

Answers (1)

nilsman
nilsman

Reputation: 396

Way to trunc string to 1000 characters is:

SELECT SUBSTR(LISTAGG(EMPLOYEE_NAME,', ') WITHIN GROUP 
    (ORDER BY EMPLOYEE_NAME), 1, 1000) AS NAMES FROM TB_EMPLOYEE;

If you want to stop conacatenating EMPLOYEE_NAME on length condition in runtime, you should write your own PL\SQL proc, LISTAGG concats all data.

Okay, if you want to make it in single query, try this. First of all, calc overall lengths of all concatenating strings plus 2 for ', ' symbols:

SELECT EMPLOYEE_NAME, SUM(LENGTH(EMPLOYEE_NAME)+2) over (ORDER BY EMPLOYEE_NAME) FROM TB_EMPLOYEE;

Then call LISTAGG for this subquery:

SELECT LISTAGG(EMPLOYEE_NAME,', ') WITHIN GROUP 
(ORDER BY EMPLOYEE_NAME) AS NAMES FROM (
  SELECT EMPLOYEE_NAME, SUM(LENGTH(EMPLOYEE_NAME)+2) over 
  (ORDER BY EMPLOYEE_NAME) slide_length FROM TB_EMPLOYEE )
WHERE slide_length < 1000;

Upvotes: 1

Related Questions