Split each arraylist/list element into tuples of ints and strings

I parsed sql insert statements into list of values like the following:

List(6606 'TIMI YURO' 'HURT' 0)
List(6607 'TIMI YURO' 'WHAT*S A MATTER BABY' 0)
List(6608 'TIMI YURO' 'MAKE THE WORLD GO AWAY' 0)
List(6609 'HELMUT ZACHARIAS' 'WHEN THE WHITE LILACS BLOOM AGAIN' 0)
List(6610 'JOHN *THE COOL GHOUL* ZACHERLE' 'DINNER WITH DRAC' 0)
List(6611 'MICHAEL ZAGER BAND' 'LET*S ALL CHANT' 0)
List(6612 'ZAGER AND EVANS' 'IN THE YEAR 2525 (EXORDIUM AND TERMINUS)' 1)
List(6613 'RICKY ZAHND / BLUEJEANERS' 'NUTTIN* FOR CHRISTMAS' 0)
List(6614 'WARREN ZEVON' 'WEREWOLVES OF LONDON' 0)
List(6615 'ZOMBIES' 'SHE*S NOT THERE' 0)

Now, for each list, I want to split ints and strings into a tuple of 4.I am not good at regex. If I do split(" "), then in some cases where I have a complex string with spaces such as WHEN THE WHITE LILACS BLOOM AGAIN which I want to maintain as a single string tuple value, it does not work. I tried .map( recordID => ( recordID(0), recordID(1), recordID(2), recordID(3) ) ) but I will get out of bound exception. I would appreciate if anyone can help.

Update: The following is the original sql statements. It is very long but I'm just posting a sample. I want to read from a file each value into tuples of 4 (int, string, string, int):

insert into songlist (id, artist, title, numone) values (6606, 'TIMI YURO', 'HURT', 0);
insert into songlist (id, artist, title, numone) values (6607, 'TIMI YURO', 'WHAT*S A MATTER BABY', 0);
insert into songlist (id, artist, title, numone) values (6608, 'TIMI YURO', 'MAKE THE WORLD GO AWAY', 0);
insert into songlist (id, artist, title, numone) values (6609, 'HELMUT ZACHARIAS', 'WHEN THE WHITE LILACS BLOOM AGAIN', 0);
insert into songlist (id, artist, title, numone) values (6610, 'JOHN *THE COOL GHOUL* ZACHERLE', 'DINNER WITH DRAC', 0);
insert into songlist (id, artist, title, numone) values (6611, 'MICHAEL ZAGER BAND', 'LET*S ALL CHANT', 0);
insert into songlist (id, artist, title, numone) values (6612, 'ZAGER AND EVANS', 'IN THE YEAR 2525 (EXORDIUM AND TERMINUS)', 1);
insert into songlist (id, artist, title, numone) values (6613, 'RICKY ZAHND / BLUEJEANERS', 'NUTTIN* FOR CHRISTMAS', 0);
insert into songlist (id, artist, title, numone) values (6614, 'WARREN ZEVON', 'WEREWOLVES OF LONDON', 0);
insert into songlist (id, artist, title, numone) values (6615, 'ZOMBIES', 'SHE*S NOT THERE', 0);

1 answer

  • answered 2017-12-11 07:08 ameer

    You can iterate through the lines of the input and do something like this

    val line = "insert into songlist (id, artist, title, numone) values (6606, 'TIMI YURO', 'HURT', 0);"
    val parseInsertRegex = raw".*values \((\d+), '(.*)', '(.*)', (\d+)\);".r
    line match {
        case parseInsertRegex(id, artist, title, numone) => (id.toInt, artist, title, numone == "1")
    }
    

    This returns me

    res0: (Int, String, String, Boolean) = (6606,TIMI YURO,HURT,false)