SQLTIP－－文字列を特定数の単語単位で改行する。

str
--------------------------------
This is not a verry long test string
Happy new year !

id string
----------------------
1 This is not a
1 verry long test
1 string
2 Happy new year
2 !


SELECT ID, STRING
FROM (SELECT level#, rnum ID,
SUBSTR (str, INSTR (str, base, 1, level#) + len,
INSTR (SUBSTR (str, INSTR (str, base, 1, level#) + len,
limit# + 1), base, -1) - 1) STRING,
INSTR (str, base, 1, level#) + len - 1 start#,
INSTR (SUBSTR (str, INSTR (str, base, 1, level#) + len,
limit# + 1), base, -1) +
INSTR (str, base, 1, level#) + len - 1 end#
FROM (SELECT LEVEL level#, limit#, base, len
FROM (SELECT MAX ( LENGTH (str)
- LENGTH (REPLACE (str, ' ')) + 1) max_part,
15 limit#, ' ' base, LENGTH (' ') len
FROM TEST)
CONNECT BY LEVEL <= max_part),
(SELECT ROWNUM rnum, ' ' || str || ' ' str,
LENGTH (str) - LENGTH (REPLACE (str, ' ')) + 1 part
FROM TEST)
WHERE level# <= part)
START WITH start# = 1
CONNECT BY PRIOR end# = start# AND PRIOR ID = ID
GROUP BY ID, level#, STRING
/