Re: split to table by space

[Brian Modra <epailty@xxxxxxxxxxxxxx>]

2010/1/3 Jamie Kahgee <jamie.kahgee@xxxxxxxxx>:
> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general