Remove Trailing Parenthetical in PostgreSQL-CodePudding

In my attempt to query a table, one of the fields has the occasional observation with a trailing parenthetical. I need to remove the parentheses and the strings they contain.

Example:
What I Have
Christopher Columbus (this is what I need to remove)
Mickey Mouse
American Gladiator (parenthetical)

What I Need
Christopher Columbus
Mickey Mouse
American Gladiator

Is there some combination of regex/trim() that can accomplish this?

CodePudding user response：

If there is no further parenthesis in the names

CREATE TABLE series (program varchar(100))

INSERT INTO series VALUES ('Christopher Columbus (this is what I need to remove)'),
('Mickey Mouse'),
('American Gladiator (parenthetical)')

SELECT left("program",
CASE WHEN POSITION('(' IN "program") = 0 tHEN LENGTH("program")
ELSE POSITION('(' IN "program") -1 END ) FROM series

| left                  |
| :-------------------- |
| Christopher Columbus  |
| Mickey Mouse          |
| American Gladiator    |

db<>fiddle here

CodePudding user response：

You can use REGEXP_REPLACE to remove everything from the first ( (and any preceding whitespace) to the end of the string:

SELECT REGEXP_REPLACE(program, '\s*\(.*$', '') AS result
FROM series

Output:

result
Christopher Columbus
Mickey Mouse
American Gladiator

Demo on dbfiddle