How to split a string in a smart way?

Question

Function string_to_array splits strings without grouping substrings in apostrophes:

# select unnest(string_to_array('one, "two,three"', ','));
 unnest
--------
 one
 "two
 three"
(3 rows)

I would like to have a smarter function, like this:

# select unnest(smarter_string_to_array('one, "two,three"', ','));
 unnest
--------
 one
 two,three
(2 rows)

Purpose.

I know that COPY command does it in a proper way, but I need this feature internally. I want to parse a text representation of rows of existing table. Example:

# select * from dataset limit 2;
 id |      name       | state  
----+-----------------+--------
  1 | Smith, Reginald | Canada
  2 | Jones, Susan    | 
(2 rows)

# select dataset::text from dataset limit 2;
           dataset            
------------------------------
 (1,"Smith, Reginald",Canada)
 (2,"Jones, Susan","")
(2 rows)

I want to do it dynamically in a plpgsql function for different tables. I cannot assume constant number of columns of a table nor a format of columns values.

@muistooshort - yes, in a certain sense. I have added en explanation. — Adam Piotrowski
– Adam Piotrowski, Commented Jun 11, 2015 at 11:09
Why would you want to parse dataset::text in SQL when you have access to lots of data types that won't require all that? — mu is too short
– mu is too short, Commented Jun 11, 2015 at 17:13
@muistooshort - I tried to explain this in the last paragraph of the question. I want to apply one algorithm to many different tables. — Adam Piotrowski
– Adam Piotrowski, Commented Jun 11, 2015 at 17:31
I'd recommend deleting this question and asking the question you really want to ask. Parsing the text representation of a row is your solution, not your question. You seem to really want to work with ROWs in Pl/pgSQL so that you don't have to parse strings at all. — mu is too short
– mu is too short, Commented Jun 11, 2015 at 18:21

klin · Accepted Answer · 2015-06-12 00:07:20Z

1

There is a nice method to transpose a whole table into a one-column table:

select (json_each_text(row_to_json(t))).value from dataset t;

If the column id is unique then

select id, array_agg(value) arr from (
    select row_number() over() rn, id, value from (
        select id, (json_each_text(row_to_json(t))).value from dataset t
        ) alias
    order by id, rn
    ) alias
group by id;

gives you exactly what you want. Additional query with row_number() is necessary to keep original order of columns.

edited Jun 12, 2015 at 0:07

answered Jun 11, 2015 at 17:59

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to split a string in a smart way?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related