[GENERAL] 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. 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? Thanks, Jamie K.
Re: [GENERAL] split to table by space
2010/1/3 Jamie Kahgee jamie.kah...@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
Re: [GENERAL] split to table by space
On Sun, Jan 3, 2010 at 9:30 AM, Brian Modra epai...@googlemail.com wrote:
2010/1/3 Jamie Kahgee jamie.kah...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] split to table by space
On Sun, Jan 3, 2010 at 9:37 AM, Allan Kamau kamaual...@gmail.com wrote:
On Sun, Jan 3, 2010 at 9:30 AM, Brian Modra epai...@googlemail.com wrote:
2010/1/3 Jamie Kahgee jamie.kah...@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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
