I have a column of values where some values contain brackets with text which I would like to remove. This is an example of what I have and what I want:

CREATE TABLE test
(column_i_have varchar(50), 
column_i_want varchar(50))

INSERT INTO test (column_i_have, column_i_want)
VALUES ('hospital (PWD)', 'hopistal'), 
('nursing (LLC)','nursing'),
('longterm (AT)', 'longterm'),
('inpatient', 'inpatient')

I have only come across approaches that use the number of characters or the position to trim the string, but these values have varying lengths. One way I was thinking was something like:

TRIM('(*',col1)

Doesn't work. Is there a way to do this in postgres SQL without using the position? THANK YOU!

CodePudding user response：

You can replace partial patterns using regular expressions. For example:

select *, regexp_replace(v, '\([^\)]*\)', '', 'g') as r
from (
  select '''hospital (PWD)'', ''nursing (LLC)'', ''longterm (AT)'', ''inpatient''' as v
) x

Result:

r
-------------------------------------------------
'hospital ', 'nursing ', 'longterm ', 'inpatient'

See example at db<>fiddle.

CodePudding user response：

Could it be as easy as:

SELECT SUBSTRING(column_i_have, '\w ') AS column_i_want FROM test

See demo

---

If not, and you still want to use SUBSTRING() to get upto but exclude paranthesis, then maybe:

SELECT SUBSTRING(column_i_have, '^(. ?)(?:\s*\(.*)?$') AS column_i_want FROM test

See demo

---

But if you really are looking upto the opening paranthesis, then maybe just use SPLIT_PART():

SELECT SPLIT_PART(column_i_have, ' (', 1) AS column_i_want FROM test

See demo

CodePudding user response：

If all the values contain "valid" brackets, then you may use split_part function without any regular expressions:

select
  test.*,
  trim(split_part(column_i_have, '(', 1)) as res
from test

column_i_have  | column_i_want | res      
:------------- | :------------ | :--------
hospital (PWD) | hopistal      | hospital 
nursing (LLC)  | nursing       | nursing  
longterm (AT)  | longterm      | longterm 
inpatient      | inpatient     | inpatient

db<>fiddle here