OraQA

The following is an interesting problem posted by Jsoftware:

Given: a string of words separated by blanks and a positive integer W of the desired width. Replace appropriate blanks in the string by the newline character, so that lines are no wider than W and each line contains all the words that fit within the line.

To focus on essentials, assume words are at most W in length, adjacent words are separated by exactly one blank, and the input string does not contain newlines.

create table t3 as
select 1 as id,
'Stop all the clocks, cut off the telephone, prevent the dog from barking with a juicy bone.
The quick brown fox jumps over the lazy dog. Assiduously avoid any and all asinine alliterations.'
as str from dual;

variable width number
exec :width := 25;

PL/SQL procedure successfully completed.

—————————————–SQL Solution——————-

SELECT regexp_replace(XMLAgg(XMLElement(X, str1||chr(10))
                                            ORDER BY root_rn),'<X>|</X><X>|</X>') DATA
FROM
(SELECT str1, id, root_rn
 FROM
 (SELECT max(str1) str1,  max(last_rn) as last_rn,  root_rn,  id
  FROM
  (SELECT ltrim(sys_connect_by_path(str,' ')) str1, rn AS last_rn,
   CONNECT_BY_ROOT rn AS root_rn, id
  FROM
  (SELECT doc.extract('/l/text()').getStringVal() str, id,
          row_number( ) over (partition by id order by rownum) as rn
     FROM t3, TABLE(xmlSequence(extract(XMLType('<doc><l>'||
              replace(str,' ','</l><l>')||'</l></doc>'),'/doc/l'))) doc
  )
  CONNECT BY PRIOR rn + 1 = rn
  AND PRIOR id = id
  AND LEVEL <= :width
 )
 WHERE length(str1) <= :width
 GROUP BY id, root_rn
)
START WITH root_rn =1
CONNECT BY PRIOR last_rn + 1 = root_rn
AND PRIOR id = id
)
GROUP BY id;

DATA
---------------------------------
Stop all the clocks, cut
off the telephone,
prevent the dog from
barking with a juicy
bone. The quick brown fox
jumps over the lazy dog.
Assiduously avoid any and
all asinine
alliterations.

SQL> exec :width := 35;

PL/SQL procedure successfully completed.

SQL> /

DATA
--------------------------------------------
Stop all the clocks, cut off the
telephone, prevent the dog from
barking with a juicy bone. The
quick brown fox jumps over the lazy
dog. Assiduously avoid any and all
asinine alliterations.

Filed under General, SQL. Follow any responses to this entry through this RSS 2.0 feed.