Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to remove double or more spaces from a string in MySQL?
You can create a function to remove double or more spaces from a string. The syntax is as follows:
DELIMITER // create function yourFunctionName(paramter1,...N) returns datatype; begin //your statement. end; // DELIMITER ;
Here’s how to create a function:
mysql> delimiter // mysql> create function function_DeleteSpaces(value varchar(200)) returns varchar(200) -> begin -> set value = trim(value); -> while instr(value, ' ') > 0 do -> set value = replace(value, ' ', ' '); -> end while; -> return value; -> END; -> // Query OK, 0 rows affected (0.20 sec) mysql> delimiter ;
Now you can call the function using a select statement. The syntax is as follows:
SELECT yourFunctionName();
Call the above function using a select statement. The above function removes the spaces from the string:
mysql> select function_DeleteSpaces(' John Smith ');The following is the output:
+--------------------------------------------------+
| function_DeleteSpaces(' John Smith ') |
+--------------------------------------------------+
| John Smith |
+--------------------------------------------------+
1 row in set (0.02 sec)The above function removes more than one spaces. Let us see another example with a new value in the function’s parameter:
mysql> select function_DeleteSpaces(' John Smith 123 ');The following is the output:
+---------------------------------------------------------+
| function_DeleteSpaces(' John Smith 123 ') |
+---------------------------------------------------------+
| John Smith 123 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Updated on: 30-Jul-2019
625 Views
Advertisements