How to Remove spaces from string in MySQL ?

MySQL String Functions :

MySQL string functions manipulate the character string data effectively.

The following table indicates each of the functions with a brief description:

CONTACTConcatenate two or more strings into a single string
INSTRReturn the position of the first occurrence of a substring in a string
ASCIIReturns the ASCII value for the specific character
LEFTGet a specified number of leftmost characters from a string
LOWERConvert a string to lowercase
LTRIMRemove all leading spaces from a string
CHARATER_LENGHTReturns the length of a string (in characters)
RIGHTGet a specified number of rightmost characters from a string
RTRIMRemove all trailing spaces from a string
SUBSTRINGExtract a substring starting from a position with a specific length.
SUBSTRING_INDEXReturn a substring from a string before a specified number of occurrences of a delimiter
FORMATFormats a number to a format like “#,###,###.##”, rounded to a specified number of decimal places
FIND_IN_SETFind a string within a comma-separated list of strings
REVERSReverses a string and returns the result
UPPERConvert a string to uppercase


Our database has a table named student information with data in the columns roll number, first name ,last name and email address .The address 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.

roll numberfirst_namelast_nameDepartment
35RajuKumarkumar. raju@

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

SELECT    first_name,    last_name,  
  REPLACE(email_address, " ", "")
 AS correct_email_addressFROM student_information;

Here is the result of the query:




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.

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 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.

Leave a Reply