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.

Be Sociable, Share!

Leave a Reply

Your email address will not be published. Required fields are marked *

This blog is kept spam free by WP-SpamFree.