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
Split a string and loop through values in MySQL Procedure?
To split a string and loop through all values in MySQL procedure, you do not need to use REPLACE() function. To understand, first create a stored procedure and after that create a table and call the stored procedure with some values. The value will be inserted into the table.
The query to create a stored procedure is as follows:
mysql> DELIMITER // mysql> CREATE PROCEDURE SP_SplitString(Value longtext) -> BEGIN -> DECLARE front TEXT DEFAULT NULL; -> DECLARE frontlen INT DEFAULT NULL; -> DECLARE TempValue TEXT DEFAULT NULL; -> iterator: -> LOOP -> IF LENGTH(TRIM(Value)) = 0 OR Value IS NULL THEN -> LEAVE iterator; -> END IF; -> SET front = SUBSTRING_INDEX(Value,',',1); -> SET frontlen = LENGTH(front); -> SET TempValue = TRIM(front); -> INSERT INTO store (allValues) VALUES (TempValue); -> SET Value = INSERT(Value,1,frontlen + 1,''); -> END LOOP; -> END // Query OK, 0 rows affected (0.22 sec) mysql> DELIMITER ;
Now create a table that stores the value of stored procedure. The query to create a table.
mysql> CREATE TABLE store( -> Id int NOT NULL AUTO_INCREMENT, -> allValues varchar(30), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.63 sec)
Display all records from the table. The query is as follows:
mysql> select *from store; Empty set (0.00 sec)
Initially we do not have any records in the table. Let us call the stored procedure using CALL command. The query is as follows:
mysql> call SP_SplitString('Hi,Hello,Good Morning,Bye');
Query OK, 1 row affected (1.02 sec)After calling the above stored procedure, let us check once again the table records. The query to display all records from the table is as follows:
mysql> select *from store;
The following is the output:
+----+--------------+ | Id | allValues | +----+--------------+ | 1 | Hi | | 2 | Hello | | 3 | Good Morning | | 4 | Bye | +----+--------------+ 4 rows in set (0.00 sec)
4K+ Views
