Remove or Replace with blank partial string in MySQL Query

I'm trying to replace only certain parts of a string via an SQL query.

The column holds data formatted as so:

United States Postal Service&nbsp;<br> (Express Mail<sup>&reg;</sup><br>---Approx. delivery time 07-Sep-2011):

I'm trying to make it export like so:

United States Postal Service (Express Mail---Approx. delivery time 07-Sep-2011):

To do this, I used CASE, like so:

SELECT 
    CASE ot.title
        WHEN 'Free' THEN ''
        WHEN '&nbsp;<br>' THEN ' '
        WHEN '<sup>&reg;</sup> ' THEN ' '
        WHEN '<br>---' THEN '---'
        ELSE ot.title
    END
FROM orders_total AS ot;

However, it won't work. I assume CASE o开发者_StackOverflownly supports handling the entire string, not just a part of it.

Any ideas?

Peace, Chris

The REPLACE function solved the issue, working subquery here:

(SELECT REPLACE(REPLACE(REPLACE(REPLACE(ot.title, '(For orders of $75.00 or more with a maximum package weight of 50  lbs ):', ''), '&nbsp;<br>', ' '), '<sup>&reg;</sup>', ' '), '<br>', '') FROM orders_total AS ot WHERE ot.orders_id = o.orders_id AND ot.class = 'ot_shipping') AS orders_shipping_class

---

case statements are like if statements, but only do direct equality checks on the full field. Your statement boils down to

if (ot.title == 'Free') {
    ' '
} else if (ot.title == '&nbsp;<br>') {
    ' '
etc...

For this sort of thing, you'll have to use nested REPLACE() calls, which gets ugly VERY fast:

SELECT REPLACE(REPLACE(REPLACE(ot.title, 'Free', ' '), '&nbsp;<br>', ' '), 'etc...', ' ') etc...

---

You'll probably want to use multiple, nested REPLACE functions here. I'll give a sample for the first 2 cases so you get the idea.

SELECT REPLACE(REPLACE(ot.title, 'Free', ''), '&nbsp;<br>', ' ')
    FROM orders_total AS ot;

---

MySQL's REPLACE() does what you're looking to do. As the other answers correctly note, to remove more than one substring, you'll need to nest a bunch of REPLACE calls inside each other.

If you're really just selecting that value, as in you're not actually using the altered string on the SQL side of things, you're probably better off selecting the text as-is and processing it with your language of choice.

---

Case is a switch statement, it is not a part of an iterator which goes through the string.

SQL retrieves the data or modifies it according to the query, but it is not meant for actual manipulation of output data or formatting it. That is what code is meant for.

If you are planning to include any of these solutions posted into your production code, take my advice and don't do it. You will regret it.

I would suggest running an update query with nested replace statements to clean up existing data and clean input data later on, before saving it to the database.