Articles Cookbook
Back to list

How to Replace Part of a String in MySQL





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


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

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


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:

  REPLACE( name, 'x', '10' ) as new_name,
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.

Honda CB750-10012-GK8-A8
Suzuki Hayabusa X798-25-28

Recommended courses:

Recommended articles:

See also:

go to top