Extract text between parenthesis from a postgres table without creating additional column

Question

I am trying to extract text between parenthesis from a column in postgres table. I am using following command. It is creating an additional blank column.

       SELECT *, SUBSTRING (col2, '\[(.+)\]') FROM table

My table looks like this:

col1   col2
1      mut(MI_0118)
2      mut(MI_0119)
3      mut(MI_0120)

My desired output is:
col1   col2
1      MI_0118
2      MI_0119
3      MI_0120

How can I extract the text without creating an additional column.

Thanks

user330315 · Accepted Answer · 2020-01-29 11:49:58Z

2

Your regex is wrong, that's why you get an empty column. You don't want square brackets, but parentheses around the search string

select col1, substring(col2, '\((.+)\)')
from input

Online example

answered Jan 29, 2020 at 11:49

user330315

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

Comments

biruk1230 · Accepted Answer · 2020-01-29 12:30:02Z

0

The * in the SELECT statement is including all columns. Then you are adding another unnamed column. If you do:

SELECT col1, SUBSTRING (col2, '\[(.+)\]') AS col2 FROM table

It will be closer to what you want.

edited Jan 29, 2020 at 12:30

biruk1230

3,1644 gold badges18 silver badges31 bronze badges

answered Jan 29, 2020 at 11:44

Nik Shenoy

12 bronze badges

2 Comments

rshar Over a year ago

It is only capturing the open parenthesis.

Nik Shenoy Over a year ago

I can't test this right now, but your regex isn't quite right. It should match your string and you will have to escape the parentheses. Something like: .+((.+))

Collectives™ on Stack Overflow

Extract text between parenthesis from a postgres table without creating additional column

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related