CODEWITHSUNDEEP
sqloracle-database
Venkataramesh Kommoju
Venkataramesh Kommoju

Reputation: 1099

Text wrapping in oracle

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.

Upvotes: 4

Views: 11684

Answers (3)

RLong
RLong

Reputation: 1

Some code I got from Martin Burbridge and modified:

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.

Upvotes: 0

Michael Ballent
Michael Ballent

Reputation: 1088

In SQL Plus you can assign column widths

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.

Output display is usually controlled by the client

Upvotes: 2

Lev Khomich
Lev Khomich

Reputation: 2247

select regexp_replace(column_name, '(.{40})', '\1' || chr(10) || chr(13))
from some_table;

Upvotes: 5

Related Questions