CODEWITHSUNDEEP
sqloracle-database
Chito
Chito

Reputation: 315

Split string by number of character in Oracle SQL

I am looking to split string at 70 character and I found below code from this link which is slightly different than I am looking. Below code is splitting character at the max of 70 using the last space as break but what I am looking is without the last space as break. I tried to make changes from this code but honestly I am still learning the regexp_substr function and unable to figure out where the last space is considered. It will be very helpful if someone guide me through this. Thanks

Code:

with val as ( 
  select 'I am also looking for similar solution but slightly different where the split occurs at 70 character and not considering last space.' str
  from dual
), words as (
  select regexp_substr(str, '[^ ]+', 1, level) w
  from val
  connect by regexp_substr(str, '[^ ]+', 1, level) is not null
), grps as (
  select * from words 
  match_recognize (
    measures 
      match_number () grp,
      count ( text.* ) word#
    all rows per match
    pattern ( init text* )
    define 
      text as sum ( length ( w ) ) + count ( text.* ) <= 70
  )
)
  select listagg ( w, ' ' ) 
           within group ( order by word# ) split_strs
  from   grps
  group  by grp
  order  by grp;

Result from above code:


What I am looking to achieve is,

Upvotes: 0

Views: 2709

Answers (3)

Chito
Chito

Reputation: 315

This one done the trick, thanks those responded before I get to this.

with val as ( 
  select 'I am also looking for similar solution but slightly different where the split occurs at 70 character and not considering last space.' str
  from dual
), words as (
  select regexp_substr(str, '(.{1})', 1, level) w
  from val
  connect by regexp_substr(str, '(.{1})', 1, level) is not null
), grps as (
  select * from words 
  match_recognize (
    measures 
      match_number () grp,
      count ( text.* ) word#
    all rows per match
    pattern ( init text* )
    define 
      text as sum ( length ( w ) ) <= 70
  )
)
  select listagg ( w, '' ) 
           within group ( order by word# ) split_strs
  from   grps
  group  by grp
  order  by grp
;

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65218

You need a Hierarchical query containing SUBSTR() function such as

 SELECT level-1 AS "Row_Number",
        SUBSTR(str,70*(level-1)+1,70*level)
   FROM val
CONNECT BY level <= CEIL(LENGTH(str)/70)

Demo

Upvotes: 3

Dima G
Dima G

Reputation: 2025

For exactly 70 chars you can use SUBSTR instead of regexp_substr.

e.g. first 70 chars = SUBSTR(val,1,70)

Upvotes: 0

Related Questions