title: Multi line text field to seperate fields in MySQL
body: <p>So I have an address TEXT field that I need to split into seperate fields, for example:
  "physical_address" field with the following content:</p>
  
  <pre><code>| physical_address |
  +------------------+
  | 123 Street Name  |
  | Suburb           |
  | City             |
  +------------------+
  </code></pre>
  
  <p>Would become:</p>
  
  <pre><code>| physical_address_1 | physical_address_2 | physical_address_3 |
  +--------------------+--------------------+--------------------+
  | 123 Street Name    | Suburb             | City               |
  +--------------------+--------------------+--------------------+
  </code></pre>
  
  <p>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.</p>
  
  <p>This sort of works, but not quite:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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");
  </code></pre>