Search code examples
sqlregexpostgresqlsubstringtrim

SQL - trimming values before bracket

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!

Solution

  • 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