how to split new line after 30 character and split character with meaning full word
like
ex: 1
Split a new line after 30 char
acter
ex 1 split character new line but not proper word. I need the result like this
Split a new line after 30
character
Assuming you want to get up to 30 characters on each line (rather than fixed 30 character offsets from the first character), here's one way to do it:
- Split the string into a row per word
- Place these in groups up to 30 characters long
- Use listagg to combine the strings back together
You can use your favourite CSV-to-rows trick for the first part, swapping commas for spaces. For example:
with vals as (
select 'Split a new line after 30 character' str
from dual
), rws as (
select regexp_substr ( str, '[^ ]+', 1, rn ) substr, str, rn
from vals, lateral (
select level rn from dual
connect by level <= length ( str ) - length ( replace ( str, ' ' ) ) + 1
)
)
You can then use match_recognize to split these into groups up to 30 character long, using a similar method as described by Keith Laker:
https://blogs.oracle.com/datawarehousing/post/managing-overflows-in-listagg The final step is to listagg the strings back together, grouped by the original string and the group calculated above
Giving:
with vals as (
select 'Split a new line after 30 character' str
from dual
), rws as (
select regexp_substr ( str, '[^ ]+', 1, rn ) substr, str, rn
from vals, lateral (
select level rn from dual
connect by level <= length ( str ) - length ( replace ( str, ' ' ) ) + 1
)
)
select listagg (
substr, ' '
) within group (
order by rn
) split_str
from rws match_recognize (
partition by str
order by rn
measures
match_number() as grp,
length ( init.substr ) + sum ( length ( thirty.substr ) ) as l
all rows per match
pattern ( init thirty* )
define
thirty as length ( init.substr ) + sum ( length ( thirty.substr ) + 1 ) <= 30
)
group by str, grp;
SPLIT_STR
Split a new line after 30
character
Note that this assumes all words are only separated by single spaces. Real-world text is often messier than this, with splits on carriage returns, punctuation, etc. In general this is a hard problem to solve!