Possible to TRIM multiple criteria from a single column?

Question

newbie here. There are station names in a column that have extra characters at the end. I'd like to trim them in PostgreSQL.

For example, this works to remove ' (Temp)' at the end.

   SELECT
   station_name,
   RTRIM(station_name,' (Temp)')     
   FROM station_data

I would also like to remove ' - SW' as well.

Example data can look something like this:

    north_station
    east_station
    south_station
    south_station (Temp)
    south_station - SW

Ideal result would look like this:

   north_station
   east_station
   south_station
   south_station
   south_station

I've tried using CASE WHEN or OR with RTRIM but either the setup/order is wrong or maybe it can't be used in conjunction? Thank you.

Answer 1

You can use the REPLACE function. This function lets you replace a specific substring with another substring. By using REPLACE twice in a row, you can remove both ' (Temp)' and ' - SW' from the station names. It's like peeling off two layers: first, you remove ' (Temp)', and then you remove ' - SW'. This leaves you with the cleaned-up station names that you're looking for.

SELECT station_name, REPLACE(REPLACE(station_name, ' (Temp)', ''), ' - SW', '') as trimmed_station_name FROM station_data;

Hope this works for you.