CODEWITHSUNDEEP
sqlpostgresql
zbreeden007
zbreeden007

Reputation: 31

Select string of text minus text in parentheses

I have a column of account names like this, the column name is location_description:

BOULDER COMM FTHLLS HOSP (021171)
DELTA MEMORIAL HOSPITAL (019991)
DENVER HEALTH MEDICAL CTR (013500)

I am using postgres and need to Select these items but remove the parentheses and numbers inside them. Final would look like this:

BOULDER COMM FTHLLS HOSP
DELTA MEMORIAL HOSPITAL
DENVER HEALTH MEDICAL CTR

Upvotes: 0

Views: 148

Answers (1)

user330315
user330315

Reputation:

You can use regexp_replace for that

select regexp_replace(the_column, '\s*\([0-9]+\)', '')
from the_table;

The regex matches for any string that starts with a ( is followed by at least one number and then is followed by a ). The \s* is there to also remove the (white)space before the (

If the text between the parentheses can contain other characters (which your sample data doesn't) then you can extend the [0-9] part to include additional characters, e.g. [0-9-] to also include a dash.

Upvotes: 1

Related Questions