Problem extracting everything between parentheses in Postgresql

Question

I am writing a Postgresql query to return everything within parentheses inside a string. The string is "Account Suspended - Labeler has tumbling names with (dca-5:anna williams,dca-6:Anna Williams)." so the ideal output should be dca-5:anna williams,dca-6:Anna Williams.

I have tried the following syntax:

SELECT SUBSTRING(SUBSTRING(an.comment,')',1),'(',-1) as new_comment
FROM public.akon_notes an
where an.comment like '%Account Suspended - Contributor has tumbling names with%'

But it returned the error message Amazon Invalid operation: invalid input syntax for integer: ")";

Could somebody advise what I did wrong there?

your example and your code won't work together -- the like statement will not find that string -- is this as intended? — Hogan
– Hogan, Commented Dec 6, 2019 at 17:42

Gordon Linoff · Accepted Answer · 2019-12-06 17:51:00Z

One method is to use split_part(). Assuming you have no other parentheses in the string:

select split_part(replace(an.comment, ')', '('), '(', 2)
from (values ('"Account Suspended - Labeler has tumbling names with (dca-5:anna williams,dca-6:Anna Williams)."'::text)) an(comment);

split_part() splits the string based on a delimited. Alas, you have two delimiters. so this replaces the ) with (. Because you want the first part after the '(', this is the second part of the string. The above can be confused by the presence of other parens in the comment -- but it may be unclear how to handle that.

Another method uses regexp_replace():

select regexp_replace(an.comment, '.*[(](.*)[)].*', '\1')
from (values ('"Account Suspended - Labeler has tumbling names with (dca-5:anna williams,dca-6:Anna Williams)."'::text)) an(comment);

This is more handy than regexp_substr() because you don't have deal with the leading and trailing parens.

This does what you want more directly. The pattern matches the entire string (it could be `'^.([)].$' to be more explicit about that). The first and last parens are escaped. The part in the middle is identified as an element in the string.

The "replace" part of regexp_replace() replaces the entire string (because the pattern matches the entire string) with the first element matched (which is the part in parentheses).

Hogan · Accepted Answer · 2019-12-06 17:47:39Z

1

You can also use regex

substring(field,'\((.*)\)')

I didn't test you might need

substring(field,'.*\((.*)\).*')

answered Dec 6, 2019 at 17:47

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Comments

Himanshu · Accepted Answer · 2019-12-06 17:48:49Z

1

Try this. The below code used Instr(..) perhaps is general n should work in postgresql or can replace instr() with strpos()

    Select.... 
   SUBSTRING(an.comment, 
    INSTR(an.comment,'(')+1,
         INSTR(an.comment,')')-1)
       FROM TABLE like...

answered Dec 6, 2019 at 17:48

Himanshu

3,9802 gold badges16 silver badges34 bronze badges

Comments

Pedro Lorentz · Accepted Answer · 2019-12-06 17:49:11Z

1

I could not test this now, but I think that you can use regex for that:

SELECT substring(an.comment from '\((.*)\)') as new_comment
FROM public.akon_notes an
where an.comment like '%Account Suspended - Contributor has tumbling names with%'

answered Dec 6, 2019 at 17:49

Pedro Lorentz

2,3562 gold badges16 silver badges19 bronze badges

Collectives™ on Stack Overflow

Problem extracting everything between parentheses in Postgresql

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related