split to table by space

2010/1/3 Jamie Kahgee <jamie.kahgee@gmail.com>:

I need a function like regexp_split_to_table where I can split a string to a
table by a space delimiter.
so:
Please Help Me
would convert to:
Please
Help
Me
However I'm stuck working w/ version 8.2.9, so I don't have the
regexp_split_to_table function. Is there any good functions that can handle
this in my version that I am unaware of?  Or does anyone know how to write
an easy function to handle this in in plpgsql or something?

I wrote one a while ago... I'll paste it below. Its not exactly
optimised, but you are welcome:

CREATE OR REPLACE FUNCTION getWords(inv text)
RETURNS text[] AS $$
DECLARE
temp text;
i integer;
len integer;
ch character(1);
outv text[] := '{}';
outlen integer := 0;
i1 integer := 0;
BEGIN
temp := trim(both ' ' from inv);
len := char_length(temp);
i := 1;
while i <= len loop
while i <= len loop
ch := cast(substring(temp from i for 1) as character(1));
exit when ch = ' ' or ch = ',' or ch = '.' or ch = '-';
i := i + 1;
end loop;

exit when i = i1;

outv[outlen] := substring(temp from i1 for (i - i1));
outlen := outlen + 1;

while i <= len loop
ch := cast(substring(temp from i for 1) as character(1));
exit when ch != ' ' and ch != ',' and ch != '.' and ch != '-';
i := i + 1;
end loop;
i1 := i;
end loop;
return outv;
END;
$$ LANGUAGE plpgsql;

--
Brian Modra Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/

On Sun, Jan 3, 2010 at 9:30 AM, Brian Modra <epailty@googlemail.com> wrote:

2010/1/3 Jamie Kahgee <jamie.kahgee@gmail.com>:

I need a function like regexp_split_to_table where I can split a string to a
table by a space delimiter.
so:
Please Help Me
would convert to:
Please
Help
Me
However I'm stuck working w/ version 8.2.9, so I don't have the
regexp_split_to_table function. Is there any good functions that can handle
this in my version that I am unaware of?  Or does anyone know how to write
an easy function to handle this in in plpgsql or something?

I wrote one a while ago... I'll paste it below. Its not exactly
optimised, but you are welcome:

CREATE OR REPLACE FUNCTION getWords(inv text)
 RETURNS text[] AS $$
DECLARE
 temp text;
 i integer;
 len integer;
 ch character(1);
 outv text[] := '{}';
 outlen integer := 0;
 i1 integer := 0;
BEGIN
 temp := trim(both ' ' from inv);
 len  := char_length(temp);
 i    := 1;
 while i <= len loop
 while i <= len loop
  ch  := cast(substring(temp from i for 1) as character(1));
  exit when ch = ' ' or ch = ',' or ch = '.' or ch = '-';
  i := i + 1;
 end loop;

 exit when i = i1;

 outv[outlen] := substring(temp from i1 for (i - i1));
 outlen := outlen + 1;

 while i <= len loop
  ch  := cast(substring(temp from i for 1) as character(1));
  exit when ch != ' ' and ch != ',' and ch != '.' and ch != '-';
  i := i + 1;
 end loop;
 i1 := i;
 end loop;
 return outv;
END;
$$ LANGUAGE plpgsql;

--
Brian Modra   Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

A peek into the extremely helpful official PG documentation
("http://www.postgresql.org/docs/8.4/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP&quot;)
yields the example below.

SELECT foo FROM regexp_split_to_table('the quick brown fox jumped over
the lazy dog', E'\\s+') AS foo;
foo
--------
the
quick
brown
fox
jumped
over
the
lazy
dog
(9 rows)

Allan

On Sun, Jan 3, 2010 at 9:37 AM, Allan Kamau <kamauallan@gmail.com> wrote:

On Sun, Jan 3, 2010 at 9:30 AM, Brian Modra <epailty@googlemail.com> wrote:

2010/1/3 Jamie Kahgee <jamie.kahgee@gmail.com>:

I need a function like regexp_split_to_table where I can split a string to a
table by a space delimiter.
so:
Please Help Me
would convert to:
Please
Help
Me
However I'm stuck working w/ version 8.2.9, so I don't have the
regexp_split_to_table function. Is there any good functions that can handle
this in my version that I am unaware of?  Or does anyone know how to write
an easy function to handle this in in plpgsql or something?

I wrote one a while ago... I'll paste it below. Its not exactly
optimised, but you are welcome:

CREATE OR REPLACE FUNCTION getWords(inv text)
 RETURNS text[] AS $$
DECLARE
 temp text;
 i integer;
 len integer;
 ch character(1);
 outv text[] := '{}';
 outlen integer := 0;
 i1 integer := 0;
BEGIN
 temp := trim(both ' ' from inv);
 len  := char_length(temp);
 i    := 1;
 while i <= len loop
 while i <= len loop
  ch  := cast(substring(temp from i for 1) as character(1));
  exit when ch = ' ' or ch = ',' or ch = '.' or ch = '-';
  i := i + 1;
 end loop;

 exit when i = i1;

 outv[outlen] := substring(temp from i1 for (i - i1));
 outlen := outlen + 1;

 while i <= len loop
  ch  := cast(substring(temp from i for 1) as character(1));
  exit when ch != ' ' and ch != ',' and ch != '.' and ch != '-';
  i := i + 1;
 end loop;
 i1 := i;
 end loop;
 return outv;
END;
$$ LANGUAGE plpgsql;

--
Brian Modra   Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

A peek into the extremely helpful official PG documentation
("http://www.postgresql.org/docs/8.4/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP&quot;)
yields the example below.

SELECT foo FROM regexp_split_to_table('the quick brown fox jumped over
the lazy dog', E'\\s+') AS foo;
 foo
--------
 the
 quick
 brown
 fox
 jumped
 over
 the
 lazy
 dog
(9 rows)

Allan

Sorry I miss understood Jamie's question where he is looking for a
substitute for "regexp_split_to_table()" function, please ignore my
previous post.

Allan.