Articles Cookbook
Back to list
MySQL

How to Replace Part of a String in MySQL

Database:

Operators:

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 ‘/’).

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.

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

Recommended courses:

Recommended articles:

See also:

go to top