oracle oracle中的文本换行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5088094/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Text wrapping in oracle
提问by Venkataramesh Kommoju
how can we wrap a text (coulmn value) basing ona stndard length of lets say 40 characters in to multi line in ORACLE SQL only.
我们如何仅在 ORACLE SQL 中将基于标准长度（例如 40 个字符）的文本（库伦值）包装到多行中。
回答by Lev Khomich
select regexp_replace(column_name, '(.{40})', '' || chr(10) || chr(13))
from some_table;
回答by Michael Ballent
In SQL Plus you can assign column widths
在 SQL Plus 中，您可以分配列宽
column column_name format a40
select column_name from table_name
The above format the output to be 40 columns wide and it would wrap anything to the next line.
上面的格式将输出设置为 40 列宽，它会将任何内容包装到下一行。
Output display is usually controlled by the client
输出显示通常由客户端控制
回答by RLong
Some code I got from Martin Burbridge and modified:
我从 Martin Burbridge 那里得到并修改了一些代码：
CREATE OR REPLACE FUNCTION line_wrap(p_str_to_wrap VARCHAR2
,p_max_linesize PLS_INTEGER
,p_indent_spaces_each_line PLS_INTEGER DEFAULT 0
,p_separator VARCHAR2 DEFAULT ' ') RETURN VARCHAR2 IS
-- This function will insert chr(10)'s (newlines) at the separator
-- nearest the specified linesize.
-- The separator will default to a space if none provided.
-- The p_indent_spaces_each_line parameter allows each line of wrapped text to be
-- indented x spaces if desired. The indent_spaces will default to 0 if none provided.
v_max_linesize PLS_INTEGER := nvl(p_max_linesize
,80);
v_indent_spaces_each_line PLS_INTEGER := nvl(p_indent_spaces_each_line
,0);
v_separator VARCHAR2(20) := nvl(p_separator
,' ');
v_str_to_wrap VARCHAR2(4000) := p_str_to_wrap || v_separator;
v_line VARCHAR2(4000);
v_position PLS_INTEGER;
v_wrapped_text VARCHAR2(4000);
v_sql_errmsg VARCHAR2(4000);
BEGIN
WHILE v_str_to_wrap IS NOT NULL
LOOP
v_line := substr(v_str_to_wrap
,1
,v_max_linesize);
v_position := instr(v_line
,v_separator
,-1);
IF v_position = 0
THEN
v_position := v_max_linesize;
END IF;
v_line := substr(v_line
,1
,v_position);
IF v_indent_spaces_each_line > 0
THEN
v_wrapped_text := v_wrapped_text || chr(10) || lpad(' '
,v_indent_spaces_each_line
,' ') || v_line;
ELSE
v_wrapped_text := v_wrapped_text || chr(10) || v_line;
END IF;
v_str_to_wrap := substr(v_str_to_wrap
,v_position + 1);
END LOOP;
RETURN v_wrapped_text;
EXCEPTION
WHEN OTHERS THEN
v_sql_errmsg := 'Error in word_wrap: ' || SQLERRM;
raise_application_error(-20001
,v_sql_errmsg);
END;
-- How to use this function in a select statement:
select line_wrap(my_string,
40,
2,
' ')
from my_table.
