- Trending Categories
- Data Structure
- Operating System
- C Programming
- 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 insert it as individual values into a MySQL table?
You can achieve this with the help of prepared statement in MySQL. First you need to create a table. The query to create a table is as follows
mysql> create table University - > ( - > UserId int, - > UniversityId int - > ); Query OK, 0 rows affected (0.64 sec)
At first, let us set values in the above-mentioned columns. Here, we have set a string with comma separated value for UserId column. We will split this and insert in the table
mysql> SET @userId = '8,9,10'; Query OK, 0 rows affected (0.00 sec) mysql> SET @UniversityId = 100; Query OK, 0 rows affected (0.00 sec) mysql> SET @myValues = REPLACE(@userId, ',', CONCAT(', ', @UniversityId, '),(')); Query OK, 0 rows affected (0.00 sec) mysql> SET @myValues = CONCAT('(', @myValues, ', ', @UniversityId, ')'); Query OK, 0 rows affected (0.00 sec) mysql> SET @insertQuery1 = CONCAT('INSERT INTO University VALUES', @myValues); Query OK, 0 rows affected (0.00 sec)
After implementing all the steps, you need to execute the above mentioned @insertQuery1.
The query is as follows
mysql> PREPARE st FROM @insertQuery1; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> EXECUTE st; Query OK, 3 rows affected (0.18 sec) Records: 3 Duplicates: 0 Warnings: 0
Now display all records from the table using select statement
mysql> select *from University;
The following is the output displaying that we have successfully split the string (8, 9, 10) and inserted them as individual values
+--------+--------------+ | UserId | UniversityId | +--------+--------------+ | 8 | 100 | | 9 | 100 | | 10 | 100 | +--------+--------------+ 3 rows in set (0.00 sec)
Now deallocate the prepared statement.
The query is as follows
mysql> DEALLOCATE PREPARE st; Query OK, 0 rows affected (0.00 sec)
- Auto insert values into a MySQL table in a range?
- Insert JSON into a MySQL table?
- MySQL INSERT INTO SELECT into a table with AUTO_INCREMENT
- Extract gender values as a string when it is stored in the table as a Boolean in MySQL
- Split a string and loop through values in MySQL Procedure?
- MySQL query for INSERT INTO using values from another table?
- How can we insert data into a MySQL table?
- Select and insert values with preceding zeros in a MySQL table
- How can we insert a new row into a MySQL table?
- INSERT INTO table if a table exists in MySQL else implement CREATE TABLE and create the table
- Insert values in a table by MySQL SELECT from another table in MySQL?
- How it is possible to insert a zero or an empty string into a MySQL column which is defined as NOT NULL?
- How can we use INSERT() function to insert a new string into the value of a column of MySQL table?