How to Remove Spaces From a String in MySQL

Problem:

You want to remove all spaces from a string in MySQL database.

Example:

Our database has a table named customer_information with data in the columns id, first_name, last_name, and email_address. The email addresses were filled out by the users manually, and some were entered with unnecessary spaces by accident. You want to remove the spaces from the email addresses.

Solution:

We will use the REPLACE function. Here is the query:

SELECT
  first_name,
  last_name,
  REPLACE(email_address, ' ', '') AS correct_email_address
FROM customer_information;

Here is the result of the query:

Discussion:

Use the REPLACE function if you want to replace all occurrences of a substring in a given string. In our example, we replace unnecessary spaces with empty values.

This function takes three arguments. The following illustrates the syntax of the function:

REPLACE(string_expression, substring, new_substring)

The first argument is the string we would like to modify. The second argument is the substring to be replaced, and the third argument is the replacement string we want to use in place of the replaced substring. We remove the unwanted spaces in our example, but the function can be used for many purposes. For instance, we may want to clean and standardize a column of telephone numbers. We can use the REPLACE function to do so.

If you want to remove more than one character from a given string, just nest the REPLACE function a second time. For example, if you have a string with unnecessary spaces and dashes, you can use the following syntax:

REPLACE(REPLACE(string_expression, ' ', ''), '-', '')

Remember that the arguments of this function in MySQL are case-sensitive, so be careful with the letters.