Back to list MySQL How to Replace Part of a String in MySQL Database: MySQL 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: SQL Basics SQL Practice Set Standard SQL Functions Recommended articles: 5 SQL Functions for Manipulating Strings 18 Useful Important SQL Functions to Learn ASAP Performing Calculations on Date- and Time-Related Values How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Replace Part of a String in SQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.