Split comma separated values into individual rows

Question

I want to create separate rows which will contain one word in each cell. These words are currently stored in several cells in one column.

Below is the format that I have

A
play, games, online, free, fun
fun, play, arcade, online
racing, funny, play

To be converted into below

B
play
games
online
free
fun
arcade
racing
funny

Please note, if one row is already created for one word it should not be repeated.

Define "cell", since databases don't have cells. Is this in a spreadsheet? — Ken White, Dec 27 '11 at 19:37
http://stackoverflow.com/questions/3928325/split-keywords-for-post-php-mysql/3929161#3929161 — Praveen Prasannan, Jun 25 '13 at 15:14

score 0 · Answer 1 · answered Dec 27 '11 at 19:47

This is something that is typically better done in something other than SQL, like java.

Pseudo code could be:

List<String> names = jdbcTemplate.query("select A from your_table", new RowMapper() {
    public Object mapRow(ResultSet resultSet, int i) throws SQLException {
        return resultSet.getString(1);
    }
});

for (String name : names) {
    String[] strings = name.split("[\\w,]");
    for (int i = 0; i < strings.length; i++) {
        String string = strings[i];
        jdbcTemplate.update("insert ignore into new_table (B) values (?)", string);
    }

}

Split comma separated values into individual rows

1 Answers1

Linked