Concatenate string rows up to length in oracle sql

Question

What I'm trying to achieve is concatenating strings up to a length of 10 with carriage return. If the row goes over length 10 then it should be added to the next concatenation row.

Example, having the following data set

SELECT '0123' col FROM DUAL
UNION ALL
SELECT '45 67' FROM DUAL
UNION ALL
SELECT '89A' FROM DUAL
UNION ALL
SELECT 'BC' FROM DUAL
UNION ALL
SELECT 'DEFGHI' FROM DUAL

What I expect for the result

SELECT '0123
45 67' col FROM DUAL
UNION ALL
SELECT '89A
BC' FROM DUAL
UNION ALL
SELECT 'DEFGHI' FROM DUAL

I'm running oracle 12.1 and I don't want to do it in PLSQL due to performance reasons. I'm dealing with higher numbers. I posted the simple example so it'll be easier. My end goal is to use listagg somehow where each row has a max of 4k chars

If you want carriage return, you need to concatenate CHR(13) to the strings. rewarding splitting up a string and adding to the next row, I don't see how that can be done in straight SQL, but maybe someone smarter than me has a solution... How many rows are you talking about, and how often do you need to do this? — OldProgrammer
– OldProgrammer, Commented Nov 23, 2020 at 22:43
What does "dealing with higher numbers" mean? thousands? millions? How will you control the order of the data? You probably need more than just that one column to ensure the intended order. Is the source table indexed in any way? — Paul Maxwell
– Paul Maxwell, Commented Nov 23, 2020 at 23:00
1. What character (or characters) do you need to use for newline? chr(10) alone, which is standard in Oracle and is the line terminator in Unix? Or chr(13) || chr(10) as in DOS/Windows? Or something else? 2. Do you need to add a newline at the end of the string too? 3. What is your Oracle version? 4. What should happen if an input string (in a single row) is LONGER than the limit already? Perhaps in your real use case (4000, not 10) that doesn't happen, but it may still be an issue if you must add a newline at the end (see earlier question). — user5683823
– user5683823, Commented Nov 23, 2020 at 23:34

user5683823 · Accepted Answer · 2020-11-23 23:53:01Z

1

Here is a match_recognize solution, which requires Oracle 12.1 or higher. I make the following additional assumptions: the newline character is chr(10) as in Unix, the last row does NOT require a newline at the end, and all input row strings have length at most equal to the limit. (The limit, 10, can be changed to a bind variable.) I assume there is also an ordering column, which I called ORD.

with
  sample_data (ord, col) as (
    select 1, '0123'   from dual union all
    select 2, '45 67'  from dual union all
    select 3, '89A'    from dual union all
    select 4, 'BC'     from dual union all
    select 5, 'DEFGHI' from dual
  )
select rn, listagg(col, chr(10)) within group (order by ord) as fragment
from   sample_data
match_recognize (
  order by ord
  measures match_number() as rn
  all rows per match
  pattern (a+)
  define  a as sum(length(col)) + count(*) - 1 <= 10
)
group  by rn
order  by rn
;

   RN  FRAGMENT
-----  ------------
    1  0123
       45 67
    2  89A
       BC
    3  DEFGHI

answered Nov 23, 2020 at 23:53

user5683823

Sign up to request clarification or add additional context in comments.

1 Comment

Jucan Over a year ago

Good assumptions. Thank you. I'll read more about match_recognize

MT0 · Accepted Answer · 2020-11-23 23:44:50Z

You can use MATCH_RECOGNIZE to group the rows and then LISTAGG to concatenate them:

SELECT LISTAGG( col, CHR(10) ) WITHIN GROUP ( ORDER BY rn ) AS col
FROM   ( SELECT ROWNUM AS rn, col FROM table_name )
MATCH_RECOGNIZE(
  ORDER     BY rn
  MEASURES
    MATCH_NUMBER() AS mno
  ALL ROWS PER MATCH
  PATTERN ( short_strings* last_string )
  DEFINE short_strings AS NEXT(LENGTH(col)) <= 10 - SUM(LENGTH(col) + 1)
)
GROUP BY mno;

Which, for the sample data:

CREATE TABLE table_name ( col ) AS
SELECT '0123'   FROM DUAL UNION ALL
SELECT '45 67'  FROM DUAL UNION ALL
SELECT '89A'    FROM DUAL UNION ALL
SELECT 'BC'     FROM DUAL UNION ALL
SELECT 'DEFGHI' FROM DUAL;

Outputs:

| COL    |
| :----- |
| 0123   |
| 45 67  |
| ------ |
| 89A    |
| BC     |
| ------ |
| DEFGHI |

db<>fiddle here

GMB · Accepted Answer · 2020-11-23 23:48:06Z

0

If you want to group pairs of adjacent rows together, then you need a column that defines the ordering of the rows. Let me assume that you have such column and that is called id.

Then, you can use a recursive query. The idea is to traverse the dataset row by row, concatenating values until the length would exceed 10, at which point a new group must sart. The the outer query returns the latest row in each group:

with 
    data (id, col, rn) as (
        select t.*, row_number() over(order by id) rn 
        from mytable t
    ),
    cte (id, rn, newcol, grp) as (
        select id, rn, col, 1 from data d where rn = 1
        union all
        select d.id, d.rn, 
            case when length(c.newcol) + length(d.col) < 10
                then c.newcol || chr(13) || d.col
                else d.col
            end,
            case when length(c.newcol) + length(d.col) < 10
                then c.grp
                else d.rn
            end
        from cte c
        inner join data d on d.rn = c.rn + 1
    )
select max(newcol) as newcol 
from cte 
group by grp order by min(id)

Demo on DB Fiddle

edited Nov 23, 2020 at 23:48

answered Nov 23, 2020 at 22:59

GMB

224k25 gold badges103 silver badges151 bronze badges

3 Comments

user5683823 Over a year ago

It seems you didn't understand the question. The requirement is to stop adding new rows (as additional lines to a single string) when the total length of the string approaches the set limit. (10 in the example, but 4000 in the real-life use case). This has nothing to do with the number of input rows, or with dividing them into groups of two at a time. Nothing from your answer will help solve the problem the OP asked. Downvote from me, as "not helpful".

GMB Over a year ago

@mathguy: ah yes you are right, I misread the question. I changed the solution to offer a recursive query, which seems to do what is asked for here.

user5683823 Over a year ago

OK, I retracted my downvote. Your new solution will work in version 11.2 or higher (perhaps you can add that to your answer). Which means it is a good answer for 11.2 - for versions >= 12.1, match_recognize will be more efficient. (Also, the recursive query can be implemented in other db products, which don't have match_recognize.)

Collectives™ on Stack Overflow

Concatenate string rows up to length in oracle sql

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related