Dividing string and move down the rest of it

Question

Can someone help me? I have a string:

This is example. This is example. This is example. This is example. This is example. This is example. This is example. This is example. This is example.

I want to cut 35 characters of it and the rest of string move to bottom line. After that I want to cut 35 characters of string in second line and rest of string move to third line. After that I want cut 35 characters of string in third line and move rest of string to fourth line. It should look like this:

This is example. This is example. T
his is example. This is example. Th
is is example. This is example. Thi
s is example. This is example. This
is example.

I tried to do thing with substring function but I have no idea how to move down rest of my string.

Do you need to split the string into more records or do you need to edit the string by assing newlines? — Aleksej, Jul 18 '18 at 14:09

Aleksej · Answer 1 · 2018-07-18T14:20:34.317

You can use regular expressions to add line separators, or whatever you need, after 35 characters; for example:

select regexp_replace(str, '(.{35})', '\1' || chr(13))
FROM (
        SELECT 'This is example. This is example. This is example. This is example. This is example. This is example. This is example. This is example. This is example.' STR
        FROM DUAL
     )

This simply gets a group of 35 chars ('(.{35})') and replaces every group with itself plus a line separator ('\1' || chr(13))

score 0 · Answer 2 · answered Jul 18 '18 at 14:24

Well, probably this is not what you're looking for, but you can write this function:

create or replace function split_n(n in number, s in varchar2) return varchar2
  is
     t varchar2(1024);
     ret varchar2(4096);
     rem varchar2(4096);
  begin
    rem := s;
    loop
      t := substr(rem, 1, n);
      ret := ret || t || chr(10);
      rem := substr(rem, n+1);
      exit when rem is null;
    end loop;
    return ret;
  end split_n;

Then you just use:

> select split_n(35, 'This is example. This is example. This is example. This is example. This is example. This is example. This is example. This is example. This is example.') from dual

score 0 · Answer 3 · answered Jul 18 '18 at 14:30

This time the big solution (Splitting string into multiple rows in Oracle) is not necessary:

   with temp as
(    select 'This is example. This is example. This is example. This is example. This is example. This is example. This is example. This is example. This is example' example  from dual)
select 
    substr(t.example, (level-1)*35+1,35)  as subtext
  , length(t.example) l
  , level lv
from 
  temp t
  connect by level <= trunc(length(t.example)/35+1)
order by level;

Ok misread the question..

score 0 · Answer 4 · answered Jul 18 '18 at 14:34

A regular expression is probably simpler, but you could use a recursive CTE or a hierarchical query to split your string into multiple rows of 35 characters each:

with t (str) as (
  select 'This is example. This is example. This is example. This is example. This is example. This is example. This is example. This is example. This is example.' from dual
)
select substr(str, (35 * (level - 1)) + 1, 35) as result
from t
connect by level <= ceil(length(str)/35);

RESULT                             
-----------------------------------
This is example. This is example. T
his is example. This is example. Th
is is example. This is example. Thi
s is example. This is example. This
 is example.

5 rows selected.

and then use listagg() (required 11gR2 or above) to stick the parts back together with a newline in between:

with t (str) as (
  select 'This is example. This is example. This is example. This is example. This is example. This is example. This is example. This is example. This is example.' from dual
)
select listagg(substr(str, (35 * (level - 1)) + 1, 35), chr(10))
  within group (order by level) as result
from t
connect by level <= ceil(length(str)/35);

RESULT                                  
----------------------------------------
This is example. This is example. T
his is example. This is example. Th
is is example. This is example. Thi
s is example. This is example. This
 is example.

1 row selected.

You don't have to use a CTE, you can repeat the string literal in the two places I've referred to str, and select from dual instead.

Dividing string and move down the rest of it

4 Answers4