Skip to Main Content
  • Questions
  • Split a new line after 30 character and split with meaning full word

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Md. Arif.

Asked: September 07, 2021 - 9:47 am UTC

Last updated: September 08, 2021 - 12:35 pm UTC

Version: 12.2

Viewed 100+ times

You Asked

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


and we said...

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!

Rating

  (1 rating)

Comments

A reader, September 07, 2021 - 4:34 pm UTC

how can i create function with this query
Chris Saxon
September 08, 2021 - 12:35 pm UTC

What exactly is it you want this function to do?

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.