How to Replace Part of a String in MySQL

Problem:

You’d like to replace part of a string with another string in MySQL.

Example:

Our database has a table named motorbike_sale with data in the id, name, and part_number columns.

We’d like to change the motorbikes’ part numbers by replacing all hyphen characters with forward slashes.

Solution 1:

SELECT name,
  REPLACE( part_number, '-', '/' ) as new_part_number
FROM motorbike_sale;

This query returns a list of motorbike names and new part numbers. Notice the forward slashes that have replaced the hyphens in the part numbers:

Discussion:

Use the MySQL REPLACE() function to replace a substring (i.e. words, a character, etc.) with another substring and return the changed string. This function takes three arguments:

• The string to change. (In our example, it’s the column part_number.)
• The substring to replace (i.e. the character ‘-’).
• The substring to insert (i.e. the character ‘/’).

Notice that this function replaces all occurrences of the substring in the given string or column. In our example, each part_number contains three hyphen characters, each one of which was replaced by a slash.

In the next example, we’ll replace all instances of ‘x’ in motorbike names with '10'.

Solution 2:

SELECT id,
  REPLACE( name, 'x', '10' ) as new_name,
  part_number
FROM motorbike_sale
WHERE id>1;

This query uses a WHERE clause to filter records for rows with an id value of 2 or greater.

Notice that the Honda motorbike’s name was changed from ‘x’ to ‘10’, but the Suzuki motorbike’s name wasn’t changed. Why not? Because REPLACE() is case-sensitive. Therefore, ‘x’ is not the same as ‘X’.In this example, ‘x’ was replaced by ‘10’, but ‘X’ was unchanged.

This query displays the new name of the Honda motorbike and the old name of the Suzuki motorbike.