Remove or Replace with blank partial string in MySQL Query

Question

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 only 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

Marc B · Accepted Answer · 2011-09-08 16:47:19Z

2

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...

answered Sep 8, 2011 at 16:47

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe Stefanelli · Accepted Answer · 2011-09-08 16:46:19Z

0

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;

answered Sep 8, 2011 at 16:46

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

Comments

John Flatness · Accepted Answer · 2011-09-08 16:49:39Z

0

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.

answered Sep 8, 2011 at 16:49

John Flatness

34.1k5 gold badges81 silver badges81 bronze badges

Comments

Artis · Accepted Answer · 2011-09-08 16:54:13Z

0

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.

answered Sep 8, 2011 at 16:54

Artis

2832 silver badges9 bronze badges

Collectives™ on Stack Overflow

Remove or Replace with blank partial string in MySQL Query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related