Re: split to table by space

2010/1/3 Jamie Kahgee <jamie(dot)kahgee(at)gmail(dot)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/