CODEWITHSUNDEEP
sqloracle-databasesplit
181
181

Reputation: 21

Dividing string and move down the rest of it

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.

Upvotes: 2

Views: 54

Answers (4)

Alex Poole
Alex Poole

Reputation: 191245

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.

Upvotes: 0

Thomas Strub
Thomas Strub

Reputation: 1285

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..

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

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

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

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))

Upvotes: 3

Related Questions