Re: split to table by space - Mailing list pgsql-general

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