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")
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
