Back to cookbooks list Articles Cookbook

How to Replace Part of a String in MySQL

  • REPLACE
  • WHERE

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.

idnamepart_number
1Harley Davidson x1245-AC2-25
2Honda CB750-x012-GK8-A8
3Suzuki Hayabusa X798-25-28

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:

namenew_ part_number
Harley Davidson x1245/AC2/25
Honda CB750-x012/GK8/A8
Suzuki Hayabusa X798/25/28

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 '/').
Discover the best interactive MySQL courses

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.

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

idnew_namepart_number
2Honda CB750-10012-GK8-A8
3Suzuki Hayabusa X798-25-28

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.

Recommended courses:

Recommended articles:

See also: