Using SELECT REPLACE with MySQL

Posted January 28th, 2009 in MySql

I recently needed to compare the content of two columns in a MySQL database that stored the large and small images for a blog post. All the small ones start with "small" followed by a number and the large ones "big" followed by a number. Enter the REPLACE() function to get rid of small/large and do the comparison! In this post I show how to use the replace function in MySQL.

Basic usage

The REPLACE() function takes three parameters:

1. the string or column name to do the replacement on
2. what to look for
3. and what to replace it with

The following example replaces the 'aaa' part of 'aaa bbb ccc' with 'xyz' and the column returned from the SQL query will contain 'xyz bbb ccc':

SELECT REPLACE('aaa bbb ccc', 'aaa', 'xyz');

If you were doing this against the column "foo" you would do this instead:

SELECT REPLACE(foo, 'aaa', 'xyz');

My example

In my case I had a column called 'image_small' and 'image_large' with example data like so:

+------------+--------------+-------------+
| content_id | image_small  | image_large |
+------------+--------------+-------------+
|          1 | small1.jpg   | big1.jpg    |
|         26 | small26.jpg  | big26.jpg   |
|         27 | small27.jpg  | big27.gif   |
|         24 | small24.jpg  | big24.jpg   |
|        419 | small208.gif | big419.gif  |
+------------+--------------+-------------+

I wanted to replace 'small' with an empty string and 'big' with an empty string in the select query, and then see if they were the same in each column. This can be done with the following query:

SELECT content_id,
      REPLACE( image_small, 'small', '' ) AS image_small,
      REPLACE( image_large, 'big', '' ) AS image_large
FROM content

The resulting data looks like this:

+------------+-------------+-------------+
| content_id | image_small | image_large |
+------------+-------------+-------------+
|          1 | 1.jpg       | 1.jpg       |
|         26 | 26.jpg      | 26.jpg      |
|         27 | 27.jpg      | 27.gif      |
|         24 | 24.jpg      | 24.jpg      |
|        419 | 208.gif     | 419.gif     |
+------------+-------------+-------------+

Extending my example with IF()

The only problem with the above example is that I now need to scan every row and see which ones do and dont' match. The query can be extended with an IF() to output 1 or 0 if the filenames (without the small and big parts) match.

SELECT content_id,
    REPLACE(image_small, 'small', '') as image_small,
    REPLACE(image_large, 'big', '') as image_large,
    IF(REPLACE(image_small, 'small', '') = REPLACE(image_large, 'big', ''), 1, 0) AS matches
FROM content

This adds an extra column called "matches" which will display 1 if the two filenames match or 0 if they don't:

+------------+-------------+-------------+---------+
| content_id | image_small | image_large | matches |
+------------+-------------+-------------+---------+
|          1 | 1.jpg       | 1.jpg       |       1 |
|         26 | 26.jpg      | 26.jpg      |       1 |
|         27 | 27.jpg      | 27.gif      |       0 |
|         24 | 24.jpg      | 24.jpg      |       1 |
|        419 | 208.gif     | 419.gif     |       0 |
+------------+-------------+-------------+---------+

Now it's much easier to see which ones have the same filenames, once the 'small' and 'big' text has been removed by combining the REPLACE() and IF() MySQL functions.