Thread: How to implement word wrap

Database column contains large string without line feeds.
How to split it with word wrap between words ?
I tried to implement word wrap using

create temp table test (line char(7));
insert into test select repeat('aa ',10);
select * from test;

Expected result is that table test contains multiple rows and every row
contains two words:

aa aa

Instead I got string too long exception.

How to implement word wrap in PostgreSql if  string contains words of any
size separated by spaces?

Andrus.

No, what you've done is asked it to insert 'aa aa aa aa aa aa aa aa aa aa'.

I suspect you're looking for:

INSERT INTO test SELECT 'aa' FROM generate_series(1,10);

Regards

Thom

Andrus <kobruleht2@hot.ee> wrote:

> Database column contains large string without line feeds.
> How to split it with word wrap between words ?
> I tried to implement word wrap using
>
> create temp table test (line char(7));
> insert into test select repeat('aa ',10);
> select * from test;
>
> Expected result is that table test contains multiple rows and every row
> contains two words:
>
> aa aa
>
> Instead I got string too long exception.
>
> How to implement word wrap in PostgreSql if  string contains words of any
> size separated by spaces?

I think you have to write a function (plpgsql, plperl, ...), counting
chars per line and change space to newline if no more space in the line.
That's not really a SQL-problem ...

Maybe there are some perl-modules for that available, i don't know.



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Just realised that's not what you're after, but my first point still stands.

Thom

> Just realised that's not what you're after, but my first point still
> stands.

Thank you.
I tried to wrap words at 15 characters using code below.

Issues:

1. Table rows places same word to multiple lines. How to remove them so that
every word appears only in single row?
2. In last select   sum(word||' ')  causes error. How to concatenate words
bact to row (inverse of unnest() function ?

Andrus.


create temp table words( id serial, word text ) on commit drop;
insert into words (word) select * from unnest(string_to_array('Quick brown
fox runs in forest.',' '));

create temp table results on commit drop as
select
  first.id as first,
  last.id as last,
  sum(length(a.word)+1) as charcount
from words a, words first, words last
where a.id between first.id and last.id
group by 1,2
having sum(length(a.word)+1)<15;

create temp table maxr on commit drop as
select
  first,
  max(charcount) as charcount
from results
group by 1;

create temp table rows on commit drop as
select
  first,
  last
from results
join maxr using (first,charcount)
order by 1;

select
  rows.first,
  sum(word||' ')
 from rows, words
where words.id between first and last
group by 1
order by 1, words.id

On 30 Mar 2010, at 11:32, Andrus wrote:

>> Just realised that's not what you're after, but my first point still stands.
>
> Thank you.
> I tried to wrap words at 15 characters using code below.


Really, write a stored procedure that accepts (text, line_length) and returns SETOF text. You could even add
hyphenationfor the appropriate language if you go that route. For the latter it's probably best to write it in C so you
canlink hyphenation libraries to your code. 

Another approach that may be viable is to use windowing functions, but I'm not so sure it's possible to have a window
thatis being defined by the data it's running over (eg. a window defined by the length of an accumulated line of text). 

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4bb1d23410411798520618!

> Really, write a stored procedure that accepts (text, line_length) and
> returns SETOF text. You could even add hyphenation for the appropriate
> language if you go that route. For the latter it's probably best to write
> it in C so you can link hyphenation libraries to your code.
>
> Another approach that may be viable is to use windowing functions, but I'm
> not so sure it's possible to have a window that is being defined by the
> data it's running over (eg. a window defined by the length of an
> accumulated line of text).

Implementations from http://sqlserverpedia.com/wiki/Word_Wrap_a_String
and from http://docstore.mik.ua/orelly/oracle/prog2/ch11_02.htm#AUTOID-10508
paragraph 11.2.2 did not work in Postgres.
I created method below. Is this best code for this ?

Andrus.

CREATE OR REPLACE FUNCTION wordwrap(line text, linelen integer)
RETURNS SETOF text as $$
DECLARE
  words text[] := string_to_array(line,' ');
  i integer;
  res text:='';

BEGIN
  if trim(line)='' then
    return next '';
    return;
    end if;
 for i IN 1 .. array_upper(words,1) LOOP
   if length(res)+length(words[i]) > linelen THEN
     return next res;
     res := '';
     END IF ;
   if res<>'' then
     res := res || ' ';
     end if;
   res := res || words[i];
   end loop;
return next res;
END
 $$ LANGUAGE plpgsql;