I have a table where one column is a string with element separated with a semicolon. in each row there are up to K elements. An example is in Table1.
I have to extract the values between the semicolon and insert them into a new table where each element will have its column, as in Table2.
My first approach would be to create a routine in Java to do it (the database is already used by a program that I'm writing to do the analysis), but is there a method to do it only in MySQL? I have found these two examples (1, 2), but my problem is a little different. I was thinking to extract an array of values from the first table and insert it in the second, but I don't know if this is possible.
Any suggestion is highly appreciated,
Table1
*****************************
* ID * Col1 *
*****************************
* 1 * abc;def;ghi;lmn;opq *
* 2 * agf;hdgk;jsud; *
* * *
* n * jd;l;qpekd;kdh; *
*****************************
Table2
*****************************************
* ID * Col1 * Col2 * Col3 * * ColK *
*****************************************
* 1 * abc * def * ghi * * *
* 2 * agf * hdgk * jsud * * *
* * * * * * *
* n * jd * l * qpekd* * *
*****************************************
1) Insert data from another table with a loop in mysql
2) mysql :: insert into table, data from another table?
Unfortunately I had to do the work in Java, I leave the solution if someone will find the same problem. But the question on how to do it in MySQL is still open
public void analyse_Logs(Connection as DB_Connection) {
String MySQL_String, messageString;
Long nInitial, nFinal, iRow;
try{ // begin try-catch for Query_Connection
this.checkConnection();
Statement MySQL_Statement = DB_Connection.createStatement();
try { // begin try-finally for Query_Statement
MySQL_String = "SELECT message FROM logs";
ResultSet MySQL_ResultSet = MySQL_Statement.executeQuery(MySQL_String);
try { // begin try-finally for Query_ResultSet
iRow = Long.Valueof(0);
while (MySQL_ResultSet.next()) {
messageString = MySQL_ResultSet.getString("message"); //for each row, extract log message
this.logMessageAnalysis(iRow, messageString); //call log analysis routine
iRow = iRow+1;
}
} finally {
try { MySQL_ResultSet.close(); }
catch (SQLException ex) { System.out.println("Error - " + ex.toString()); }
} // end try-finally for Query_ResultSet
} finally {
try { MySQL_Statement.close(); }
catch (SQLException ex) { System.out.println("Error - " + ex.toString()); }
} // end try finally for Query_Statement
} catch(SQLException ex) {
System.out.println("Error - " + ex.toString());
} // end try-catch for Query_Connection
}
public void logMessageAnalysis(Long iRow, String logMessage, Connection DB_Connection){
//split string in array
String inputArray[] = logMessage.split(";");
String[] outputArray = new String[16];
outputArray[0] = String.valueOf(iRow);
for(int i=1; i<inputArray.length; i=i+1){
if (inputArray[i].length() > 45) {
inputArray[i] = inputArray[i].substring(0, 45);
}
outputArray[i] = inputArray[i];
}
try{ // begin try-catch for Query_Connection
this.checkConnection();
Statement MySQL_Statement = DB_Connection.createStatement();
try { // begin try-finally for Query_Statement
String Query_String = "INSERT INTO logs_2 VALUES ('";
for (int i=0; i<15; i=i+1) {
Query_String = Query_String + outputArray[i] + "','";
}
Query_String = Query_String + outputArray[15] + "')";
MySQL_Statement.executeUpdate(Query_String);
} finally {
try { MySQL_Statement.close(); }
catch (SQLException ex) { System.out.println("Error - " + ex.toString()); }
} // end try finally for Query_Statement
} catch(SQLException ex) {
System.out.println("Error - " + ex.toString());
} // end try-catch for Query_Connection
}
