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:
| Name | Description |
|---|---|
| CONTACT | Concatenate two or more strings into a single string |
| INSTR | Return the position of the first occurrence of a substring in a string |
| ASCII | Returns the ASCII value for the specific character |
| LEFT | Get a specified number of leftmost characters from a string |
| LOWER | Convert a string to lowercase |
| LTRIM | Remove all leading spaces from a string |
| CHARATER_LENGHT | Returns the length of a string (in characters) |
| RIGHT | Get a specified number of rightmost characters from a string |
| RTRIM | Remove all trailing spaces from a string |
| SUBSTRING | Extract a substring starting from a position with a specific length. |
| SUBSTRING_INDEX | Return a substring from a string before a specified number of occurrences of a delimiter |
| FORMAT | Formats a number to a format like “#,###,###.##”, rounded to a specified number of decimal places |
| FIND_IN_SET | Find a string within a comma-separated list of strings |
| REVERS | Reverses a string and returns the result |
| UPPER | Convert a string to uppercase |
Example:
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 number | first_name | last_name | Department |
|---|---|---|---|
| 14 | Raj | Rai | rai. raj@example.com |
| 35 | Raju | Kumar | kumar. raju@ example.com |
| 26 | Kiran | Kumari | kuamri .kiran@example.com |
| 17 | Arjun | Kumar | kumar. arjun@example.com |
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:
| first_name | last_name | email_address |
|---|---|---|
| Raj | Rai | rai.raj@example.com |
Raju | kumar | kumar.raju@example.com |
| Kiran | kumari | kiran.kumari@example.com |
| Arjun | kumar | kumar.arjun@example.com |
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.
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.