How to insert a String delimited by special chars into table

Question

I'd like to do INSERT to a table. This table is a simple table.

student_id      subject_id     student_name    student_report
      S001             M01           Albert                A+
      S001             M02           Albert                A
      S002             M04            Shana                B+

I have a datasource from a single string delimited by special chars. Something like this.

S003@@@M02@@@Meru@@@C@@@S003@@@M03@@@Meru@@@S004@@@M01@@@Haschel@@@D

How do I insert that kind of string to the table? Any answers or clue or reference greatly appreciated. Thank you in advance

The table looks like after insert:

    student_id      subject_id     student_name    student_report
          S001             M01           Albert                A+
          S001             M02           Albert                A
          S002             M04            Shana                B+
          S003             M02             Meru                C
          S003             M02             Meru                NULL //no data here
          S004             M01          Haschel                D

http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 — e4c5, Nov 29 '16 at 07:10
@e4c5 for my case it's fine. I have forced user to input specific value in the form and also it's for local use. — Simba, Nov 29 '16 at 07:13

score 0 · Accepted Answer · answered Nov 29 '16 at 07:18

0

You need to split this string. Use explode function like this:

  $data = explode('@@@',$stingValue,);

$data will be having all the values. Open database connection, bind write a query, bind parameters and execute it.

answered Nov 29 '16 at 07:18

Danyal Sandeelo

12,196
10
47
78

S003,S003 and S004 should be a new row. How do I differentiate those – Simba Nov 29 '16 at 07:24
probably there would be some easier way to solve your problem, you will have to handle this thing on application layer but there needs to be a way to separate each record..may be you can add another string to different the records... something like this `S003@@@M02@@@Meru@@@C#STRING-END#S003` so split witl string end first so that you may know the total records you will get and then perform the above mentioned logic – Danyal Sandeelo Nov 29 '16 at 07:28

Bernd Buffen · Answer 2 · 2016-11-29T07:25:11.800

you can it do with a query like this:

SELECT 
    REVERSE(SUBSTRING_INDEX(REVERSE(
    SUBSTRING_INDEX('S003@@@M02@@@Meru@@@C@@@S003@@@M03@@@Meru@@@S004@@@M01@@@Haschel@@@D','@@@',3)),'@@@',1));

To INSERT

INSERT INTO YOUR_TABLE
    SET
        student_id = REVERSE(SUBSTRING_INDEX(REVERSE(
         SUBSTRING_INDEX('S003@@@M02@@@Meru@@@C@@@S003@@@M03@@@Meru@@@S004@@@M01@@@Haschel@@@D','@@@',1)),'@@@',1)),
       subject_id = REVERSE(SUBSTRING_INDEX(REVERSE(
         SUBSTRING_INDEX('S003@@@M02@@@Meru@@@C@@@S003@@@M03@@@Meru@@@S004@@@M01@@@Haschel@@@D','@@@',2)),'@@@',1));

sample

mysql> SELECT      REVERSE(SUBSTRING_INDEX(REVERSE(     SUBSTRING_INDEX('S003@@@M02@@@Meru@@@C@@@S003@@@M03@@@Meru@@@S004@@@M01@@@Haschel@@@D','@@@',1)),'@@@',1)) as reselt;
+--------+
| reselt |
+--------+
| S003   |
+--------+
1 row in set (0,00 sec)

mysql> SELECT      REVERSE(SUBSTRING_INDEX(REVERSE(     SUBSTRING_INDEX('S003@@@M02@@@Meru@@@C@@@S003@@@M03@@@Meru@@@S004@@@M01@@@Haschel@@@D','@@@',2)),'@@@',1)) as reselt;
+--------+
| reselt |
+--------+
| M02    |
+--------+
1 row in set (0,00 sec)

mysql> SELECT      REVERSE(SUBSTRING_INDEX(REVERSE(     SUBSTRING_INDEX('S003@@@M02@@@Meru@@@C@@@S003@@@M03@@@Meru@@@S004@@@M01@@@Haschel@@@D','@@@',3)),'@@@',1)) as reselt;
+--------+
| reselt |
+--------+
| Meru   |
+--------+
1 row in set (0,00 sec)

That string is in PHP. I'd like to insert that to a MySQL table. — Simba, Nov 29 '16 at 07:22

score 0 · Answer 3 · answered Nov 29 '16 at 07:46

if your String contains value for multiple rows, you should add a line end parameter to signify the starting of new row's values instead of delimeters. e.g. :

Use S003@@@M02@@@Meru@@@C$$$S003@@@M03@@@Meru$$$S004@@@M01@@@Haschel@@@D

instead of S003@@@M02@@@Meru@@@C@@@S003@@@M03@@@Meru@@@S004@@@M01@@@Haschel@@@D

if any field is empty there there should be delimeters twice without any value between them.

e.g.

Use S003@@@M03@@@Meru@@@@@@

Instead of Use S003@@@M03@@@Meru@@@

Add a end of text delimeter to signify no more records are there. such as ##

Now it will be easy for you, loop each line to get the string that has values for each row of database and stop processing when you have reached end of text:

The logic for the parser somewhat goes like this (not php just pseudocode):

while(character != <end-of-line-character>)
{
    if( character != <new-line-character> )
    {
        string = string . character
    }
    else
    {
        value_to_insert_in_database = explode("@@@", string)
        perform_insertion_in_database with value_to_insert_in_database
        string = ""
    }
}

Thanks and regards

How to insert a String delimited by special chars into table

3 Answers3