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.

How to use Raw SQL Queries in Symfony 2

hashtag-doctrine-dogSometimes, usually when you first start with Symfony 2 (but there are other times too), you just want to get access to good old raw SQL.

I have experimented with a few different ways, and – as with many Symfony issues – the documentation is either shonky, or worst, so cryptic it requires a Mensa-like IQ level to decode just WTF these guys are talking about.

Now, if you are new to Symfony 2, before you go about using raw SQL for everything, do yourself a favour and make sure you learn Doctrine.

And then only if you absolutely must go native SQL, then use the following:

    public function foobar($foobar)
    {
      $stmt = $this->getEntityManager()
                   ->getConnection()
                   ->prepare('SELECT COUNT(id) AS num, foo FROM bar WHERE foobar = :foobar GROUP BY foo');
      $stmt->bindValue('foobar', $foobar);
      $stmt->execute();
      return $stmt->fetchAll();
    }

I love this, and I have used it with great success!, but the credit is not mine, it belongs to a user called althaus on the old symfony forums.