Split MySQL Column On Tabs To New Columns

Disclaimer: this is a horrible hack.

Ok, so I had a requirement to take a MySQL data set where there was a column called ‘address’ which contained an un-normalised, imported address that looked something like this:

Derby House
Lytham Road
Fulwood
Preston
Lancashire
PR2 8JE

And turn that into:

address1: Derby House
address2: Lytham Road
address3: Fulwood
address4: Preston
address5: Lancashire
address6: PR2 8JE

Ok, so that’s the plan.

Before we go any further, yes, I am fully aware this is not a great solution. For example, what if the next address only contained 5 rows – that would mean the post code ends up in address5.

That’s why I said this is a horrible hack, right at the beginning there. So don’t flame me.

Anyway, this *may* be useful to you.

UPDATE `your_table_name` SET
    `address1` = IF(
        LOCATE('\n', `address`) > 0,
        SUBSTRING(`address`, 1, LOCATE('\n', `address`) - 1),
        `address1`
    ),   
    `address2` = IF(
        LOCATE('\n', `address`) > 0,
        SUBSTRING(`address`, LOCATE('\n', `address`) + 1 ),
        NULL
    ),     
    `address3` = IF(
        LOCATE('\n', `address2`) > 0,
        SUBSTRING(`adcampsite_resultsdress2`, LOCATE('\n', `address2`) + 1 ),
        NULL
    ),
    `address4` = IF(
        LOCATE('\n', `address3`) > 0,
        SUBSTRING(`address3`, LOCATE('\n', `address3`) + 1 ),
        NULL
    ),
    `address5` = IF(
        LOCATE('\n', `address4`) > 0,
        SUBSTRING(`address4`, LOCATE('\n', `address4`) + 1 ),
        NULL
    ),
    `address6` = IF(
        LOCATE('\n', `address5`) > 0,
        SUBSTRING(`address5`, LOCATE('\n', `address5`) + 1 ),
        NULL
    ),
    
    `address2` = IF(
        LOCATE('\n', `address1`) > 0,
        SUBSTRING(`address1`, 1, LOCATE('\n', `address1`) - 1),
        `address1`
    ),  
    `address3` = IF(
        LOCATE('\n', `address3`) > 0,
        SUBSTRING(`address3`, 1, LOCATE('\n', `address3`) - 1),
        `address3`
    ),  
    `address4` = IF(
        LOCATE('\n', `address4`) > 0,
        SUBSTRING(`address4`, 1, LOCATE('\n', `address4`) - 1),
        `address4`
    ),  
    `address5` = IF(
        LOCATE('\n', `address5`) > 0,
        SUBSTRING(`address5`, 1, LOCATE('\n', `address5`) - 1),
        `address5`
    ),  
    `address6` = IF(
        LOCATE('\n', `address6`) > 0,
        SUBSTRING(`address6`, 1, LOCATE('\n', `address6`) - 1),
        `address6`
    )
    ;

What this is doing, in a nut shell, is splitting the row by new line (\n), one step at a time, then repeating the split, on the new column.

So in our case, taking the original example, the following happens:

‘address’ column:

Derby House
Lytham Road
Fulwood
Preston
Lancashire
PR2 8JE

After the first if statement, ‘address1’ becomes:

Derby House

All the other columns are blank.

After the second if statement, ‘address1’ doesn’t change, but ‘address2’ becomes:

Lytham Road
Fulwood
Preston
Lancashire
PR2 8JE

After the second if statement, ‘address1’ and ‘address2’ don’t change, but ‘address3’ becomes:

Fulwood
Preston
Lancashire
PR2 8JE

And so on, until all the columns are populated (up to address6).

Then, after this bit:

    `address6` = IF(
        LOCATE('\n', `address5`) > 0,
        SUBSTRING(`address5`, LOCATE('\n', `address5`) + 1 ),
        NULL
    ),

The code ‘loops’ back through and removes anything in each column after the first new line (\n).

There is likely a more elegant solution to this problem. But, in my case, finding that solution wasn’t justified as this hack achieves the desired outcome.

Hopefully it helps someone, somewhere, somehow.