Tutorial :MySQL: select words as rows even som are “new line” separated in one field



Question:

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


Solution:1

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.


Solution: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.


Solution:3

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »