MySQL: select words as rows even som are "new line" separated in one field

Question

Hi

I have a table with a field where words are written separated with new lines. So a select on this single field from to rows will output 3 lines for first row and 2 lines for second row:

Row1    designationer
        nye kolonier
        mindre byer
Row2    udsteder
        bopladser

I would like to do a select that select all these lines as if they had been rows in the table like:

SELECT do_the_split(field) FROM table

so the result would be more like:

Row1    designationer
Row2    nye kolonier
Row3    mindre byer
Row4    udsteder
Row5    bopladser

is there any way to do this in MySQL?

BR. Anders

UPDATE: There are correct answers below but ended up doing it in a semi-manual way. I did this:

1. exporting the the column to a csv
2. Open file in notepad and remove the pings around each line
3. now each word is on a new line
4. save as csv
5. import into database where each line will be a new row

Br. Anders

Answer 1

I don't know the in-and-outs of mySQL to be of any help in that but what's stopping you from doing the splitting in the application layer? Say, you load your rows, as they are, in Java (or PHP or whatever) and then

String row = <fetch row from resultset>;
String[] individualItems = row.split("\n");

If you could store the values like that in your DB, you could just as well retrieve them like that.

Answer 2

I've faced the same problem and the only two ways I know of getting the kind of collection of words you want are stored procedures (which is what I did, although with the Derby DB) or a script/program.

Answer 3

You can use a stored procedure - similar to what this person did - to accomplish this, essentially utilizing a temp table.

Certainly you could accomplish this locally in your app, as MasterPeter has suggested.