Postgresql - Split data from a column into two columns

Question

I have a small Porstgre database where I a have a table with several columns. One of those columns contains data like this : test1 (80%) test2 (21%) test3 (40%) etc ...

What I would like to do, is to be able to move that percentage to another column so the data will look like :

TEST_COLUMN | PERCENTAGE_COLUMN

test1       | 80%

test2       | 21%

etc...

Knowing that I have over 10k records, it would be helpful if there is a way to achieve this without having to move anything manually. Thank in advance

Lukasz Szozda · Accepted Answer · 2017-12-02 19:07:06Z

3

You could use REGEXP:

SELECT  col ,TRIM(REGEXP_REPLACE(col, '(\(\d+%\))$', '')) AS test_column
       ,(REGEXP_MATCHES(col, '(\d+%)\)$'))[1] AS percentage_column
FROM t;

DBFiddle Demo

edited Dec 2, 2017 at 19:07

answered Dec 2, 2017 at 19:03

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

1 Comment

Imad El Hitti Over a year ago

I do like this answer as it's easier than the first one, and it worked well. Thank sir

Vao Tsun · Accepted Answer · 2017-12-02 18:59:34Z

2

you can just regexp it, or other text process, eg:

t=# with a(v) as (values('test1 (80%)'),('test2 (21%)'),('test3 (40%)'))
, p as (select v,string_to_array(v,'(') ar from a)
select v, ar[1],translate(ar[2],')','') from p;
      v      |   ar   | translate
-------------+--------+-----------
 test1 (80%) | test1  | 80%
 test2 (21%) | test2  | 21%
 test3 (40%) | test3  | 40%
(3 rows)

answered Dec 2, 2017 at 18:59

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

1 Comment

Imad El Hitti Over a year ago

I didn't know we could do such thing and use regex in sql. Thank for your answer but looks a bit complicated tho.

Collectives™ on Stack Overflow

Postgresql - Split data from a column into two columns

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related