On Sun, Jan 3, 2010 at 9:37 AM, Allan Kamau <kamauallan@xxxxxxxxx> wrote:
> On Sun, Jan 3, 2010 at 9:30 AM, Brian Modra <epailty@xxxxxxxxxxxxxx> wrote:
>> 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
>>
>
> 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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
