Thread: Regular expression that splits CSV string into table

Regular expression that splits CSV string into table

From
Nick
Date:
What would be the regexp_split_to_table pattern that splits a comma
separated string into a table? Im having trouble when a string
contains commas or there are commas at the beginning or end

String
',one,two,''three,four'',five,six,'

Should return
,one
two
three,four
five
six,

Re: Regular expression that splits CSV string into table

From
bricklen
Date:
On Fri, Sep 10, 2010 at 3:43 PM, Nick <nboutelier@gmail.com> wrote:
> What would be the regexp_split_to_table pattern that splits a comma
> separated string into a table? Im having trouble when a string
> contains commas or there are commas at the beginning or end
>
> String
> ',one,two,''three,four'',five,six,'
>
> Should return
> ,one
> two
> three,four
> five
> six,

You can roll your own function, or try regexp_split_to_table, though
you will probably have to use a different delimiter if you don't want
it to break on "three,four".
Eg.

select regexp_split_to_table('"one","two","three,four","five"',',');
 regexp_split_to_table
-----------------------
 "one"
 "two"
 "three
 four"
 "five"

Re: Regular expression that splits CSV string into table

From
Nick
Date:
Yes, that gets down to the root of my question... what is the
expression that would properly split the values? -Nick

On Sep 10, 4:43 pm, brick...@gmail.com (bricklen) wrote:
> On Fri, Sep 10, 2010 at 3:43 PM, Nick <nboutel...@gmail.com> wrote:
> > What would be the regexp_split_to_table pattern that splits a comma
> > separated string into a table? Im having trouble when a string
> > contains commas or there are commas at the beginning or end
>
> > String
> > ',one,two,''three,four'',five,six,'
>
> > Should return
> > ,one
> > two
> > three,four
> > five
> > six,
>
> You can roll your own function, or try regexp_split_to_table, though
> you will probably have to use a different delimiter if you don't want
> it to break on "three,four".
> Eg.
>
> select regexp_split_to_table('"one","two","three,four","five"',',');
>  regexp_split_to_table
> -----------------------
>  "one"
>  "two"
>  "three
>  four"
>  "five"
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general


Re: Regular expression that splits CSV string into table

From
Alvaro Herrera
Date:
Excerpts from Nick's message of vie sep 10 20:36:24 -0400 2010:
> Yes, that gets down to the root of my question... what is the
> expression that would properly split the values? -Nick

The only idea that comes to mind right now is to remove them before
processing the rest of the string, and put them back to the first and
last element if they were removed.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Regular expression that splits CSV string into table

From
Nick
Date:
I dont mind if the commas are at the beginning and end, im more
concerned about "three,four" staying in one row because its surrounded
by quotes. -Nick

On Sep 10, 6:03 pm, alvhe...@commandprompt.com (Alvaro Herrera) wrote:
> Excerpts from Nick's message of vie sep 10 20:36:24 -0400 2010:
>
> > Yes, that gets down to the root of my question... what is the
> > expression that would properly split the values? -Nick
>
> The only idea that comes to mind right now is to remove them before
> processing the rest of the string, and put them back to the first and
> last element if they were removed.
>
> --
> Álvaro Herrera <alvhe...@commandprompt.com>
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general


Re: Regular expression that splits CSV string into table

From
Jeff Davis
Date:
On Fri, 2010-09-10 at 18:11 -0700, Nick wrote:
> I dont mind if the commas are at the beginning and end, im more
> concerned about "three,four" staying in one row because its surrounded
> by quotes. -Nick

It doesn't sound like a regex is the best solution here. Why not write a
function in a language that offers a CSV library, like python?

Regards,
    Jeff Davis