Re: split to table by space

On Sun, Jan 3, 2010 at 9:30 AM, Brian Modra <epailty(at)googlemail(dot)com> wrote:
> 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/
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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