Question 90725 in PostgreSQL Question posted in
The official documentation can be found here.

How to split two strings separated by a ; in two new columns – Postgresql

So I have a command where for example

SELECT something, string_agg(other, ';') FROM table
GROUP BY something HAVING COUNT(*)>1;

but I don’t know how to separate in two columns, because it doesn’t see string_agg as a column.

This is my original

something | other |         
--------+--------+
 example  | yes, no   |  
 using  | why, what  |

and I would like this please

something | other | new        
--------+--------+------
 example  | yes   | no     
 using  | why     | what
Tags:

3

Answers


  1. 0

    We can use regular expressions here:

    SELECT
    something,
    SUBSTRING(other FROM '[^,]+') AS other,
    REGEXP_REPLACE(other, '.*,[ ]*', '') AS new
FROM yourTable;
    Login or Signup to reply.
  2. 0

    I would aggregate this into an array:

    select something, 
       others[1] as other, 
       others[2] as "new"
from (
  SELECT something, array_agg(other) as others
  FROM table
  GROUP BY something 
  HAVING COUNT(*)>1
) x
    Login or Signup to reply.
  3. 0

    Alternative: Just use split_part() function. I combined with trim() function to remove leading/trailing spaces. (see demo)

    select something
     , trim(split_part(other, ',', 1)) other
     , trim(split_part(other, ',', 2)) new 
  from table;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search