Обсуждение: split to table by space
I need a function like regexp_split_to_table where I can split a string to a table by a space delimiter.
Thanks,
Jamie K.
so:
Please Help Me
would convert to:
Please
Help
Me
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?
Thanks,
Jamie K.
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/
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
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.