Multi line text field to seperate fields in MySQL

Question

So I have an address TEXT field that I need to split into seperate fields, for example: "physical_address" field with the following content:

| physical_address |
+------------------+
| 123 Street Name  |
| Suburb           |
| City             |
+------------------+

Would become:

| physical_address_1 | physical_address_2 | physical_address_3 |
+--------------------+--------------------+--------------------+
| 123 Street Name    | Suburb             | City               |
+--------------------+--------------------+--------------------+

Now this is just temporary because I need to import it into an app in the above format, so a select statement would be the best option - though I imagine it wont be that simple.

This sort of works, but not quite:

SELECT
physical_address,
SUBSTRING_INDEX(physical_address, CHAR(10), -5) AS a,
SUBSTRING_INDEX(physical_address, CHAR(10), -4) AS b,
SUBSTRING_INDEX(physical_address, CHAR(10), -3) AS c,
SUBSTRING_INDEX(physical_address, CHAR(10), -2) AS d,
SUBSTRING_INDEX(physical_address, CHAR(10), -1) AS e
FROM clients LIMIT 5

With a 5 line TEXT value, works perfectly, anything less, and you will see the first few field(s) (depending on lines) will repeat values. Can't setup a SQL Fiddle for this cos it appears to be cleaning up my return feeds, so here is the example schema:

CREATE TABLE clients (id int unsigned auto_increment primary key, physical_address text);

INSERT INTO clients (id,physical_address) VALUES (1,"123 Street,\r\n123 Suburb,\r\n123 City");
INSERT INTO clients (id,physical_address) VALUES (2,"456 Street,\r\n456 Suburb,\r\n456 City,\r\n456 Province");
INSERT INTO clients (id,physical_address) VALUES (3,"789 Street,\r\n789 Suburb,\r\n789 City,\r\n789 Province,\r\n789 Country");

Answer 1

Assuming that you have some way to identify each address to a specific user, etc. If so, then you could use user-defined variables to assign a row number and then pivot the data using an aggregate function with a CASE expression:

select id,
  max(case when rn=1 then physical_address end) Physical_address1,
  max(case when rn=2 then physical_address end) Physical_address2,
  max(case when rn=3 then physical_address end) Physical_address3
from
(
  select t.physical_address,
    id,
    @row:=case when @prev=id then @row else 0 end +1 rn,
    @prev:=id
  from yourtable t
  cross join (select @row:=0, @prev:=0)r
  order by t.id
) src
group by id
order by id

See SQL Fiddle with Demo.

If the address data is in a single row, then you will need to split the data first into multiple rows and then you can rotate it into columns.

I created a function and a procedure that will be used to split the data. I based my code off of @Johan's answer here.

First, I created an alternate version of the clients table:

CREATE TABLE clients_new
    (id int,`physical_address` varchar(256))
;

Then I created the function/procedure:

CREATE FUNCTION strSplit(x VARCHAR(5000), delim VARCHAR(12), pos INTEGER) 
RETURNS VARCHAR(256)
BEGIN
  DECLARE output VARCHAR(256);
  SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos)
                 , LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1)
                 , delim
                 , '');
  IF output = '' THEN SET output = null; END IF;
  RETURN output;
END //


CREATE PROCEDURE BadTableToGoodTable()
BEGIN
  DECLARE i INTEGER;

  SET i = 1;
  REPEAT
    INSERT INTO Clients_new (id, physical_address)
      SELECT id, strSplit(physical_address, '\r\n', i) 
      FROM Clients
      WHERE strSplit(physical_address, '\r\n', i) IS NOT NULL;
    SET i = i + 1;
    UNTIL ROW_COUNT() = 0
  END REPEAT;
END //

To execute the procedure, you will use:

call BadTableToGoodTable;

Once the procedure is done splitting the data, then you can use the original query that I provided with some minor edits to include the new table and the additional columns if needed:

select id,
  max(case when rn=1 then physical_address end) Physical_address1,
  max(case when rn=2 then physical_address end) Physical_address2,
  max(case when rn=3 then physical_address end) Physical_address3,
  max(case when rn=4 then physical_address end) Physical_address4,
  max(case when rn=5 then physical_address end) Physical_address5
from
(
  select t.physical_address,
    id,
    @row:=case when @prev=id then @row else 0 end +1 rn,
    @prev:=id
  from clients_new t
  cross join (select @row:=0, @prev:=0)r
  order by t.id
) src
group by id
order by id

Answer 2

You can select everything and then slice it using explode("/n") in php. Next save it to database as you wish. Its not possible to do it from mySQL level