MySQL: select words as rows even som are "new line" separated in one field
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 b开发者_如何学JAVAelow but ended up doing it in a semi-manual way. I did this:
- exporting the the column to a csv
- Open file in notepad and remove the pings around each line
- now each word is on a new line
- save as csv
- import into database where each line will be a new row
Br. Anders
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.
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.
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.
精彩评论