Mysql split column string into rows

A MySQL recipe, that you can use to split a cell value by a known separator into different rows, in some way similar to the PHP explode function or split in PERL.

To turn this:

id value
1 4,5,7
2 4,5
3 4,5,6
…. ….

Into this

id value
1 4
1 5
1 7
2 4
2 5
3 4
3 5
3 6
…. ….

You can simply write and call a stored procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS explode_table $$
CREATE PROCEDURE explode_table(bound VARCHAR(255))

  BEGIN

    DECLARE id INT DEFAULT 0;
    DECLARE value TEXT;
    DECLARE occurance INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE splitted_value INT;
    DECLARE done INT DEFAULT 0;
    DECLARE cur1 CURSOR FOR SELECT table1.id, table1.value
                                         FROM table1
                                         WHERE table1.value != '';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    DROP TEMPORARY TABLE IF EXISTS table2;
    CREATE TEMPORARY TABLE table2(
    `id` INT NOT NULL,
    `value` VARCHAR(255) NOT NULL
    ) ENGINE=Memory;

    OPEN cur1;
      read_loop: LOOP
        FETCH cur1 INTO id, value;
        IF done THEN
          LEAVE read_loop;
        END IF;

        SET occurance = (SELECT LENGTH(value)
                                 - LENGTH(REPLACE(value, bound, ''))
                                 +1);
        SET i=1;
        WHILE i <= occurance DO
          SET splitted_value =
          (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(value, bound, i),
          LENGTH(SUBSTRING_INDEX(value, bound, i - 1)) + 1), ',', ''));

          INSERT INTO table2 VALUES (id, splitted_value);
          SET i = i + 1;

        END WHILE;
      END LOOP;

      SELECT * FROM table2;
    CLOSE cur1;
  END; $$

Then you simply call it

CALL explode_table(',');
There it is the bare bones. From here it’s simple to adapt and build to your own needs, like adding some kind of filter parameter, order, etc… if your main interface to Mysql is PHPMyAdmin (as of now) forget it, its rubish with this procedures queries, you can use own MySQL GUI – MySQL Workbench – to interface with, or rely on the old CLI ‘mysql’ command, just put the stored procedure definition in a file and load it with a redirect:

mysql -u username -p -D databasename < procedure_definition_file.txt

Also remember:

  • if backups are made with mysqldump, use the –routines switch so the stored procedure definition goes in the dumps.
  • works mysql >= 5.0 only
  • performance, normalization and concurrency – this is not the correct way to do a many to many relationship with a RDBS, you should use a relationship table, and joins to work with it.
  • OK, so your project manager/marketing/boss changed the game rules at the very last moment, and to implement it correctly you must rework a lot of code, i understand 🙂 but even then enter this road at your own peril.

Rework by 37 Signals

Like Gordon Gekko once said “Because everyone is drinking the same Kool Aid“, and just because everybody in the business (the web business i mean) is drinking “Rework” by 37 Signals, i also drinked it too…. so what’s my taste of this book?

It’s a complex taste book, not because it digs deep the rabbit-hole, but because it (tries to) speak all things about the business universe, it goes from (unordered list) planning, to meetings, to time management, customer management, task prioritization, hiring and firing, office policies, marketing, product building, product minimalism, workaholism, by-products, productivity, startups, etc, etc….

It’s filled with common sense (is not so common) and Lapalissades, witch makes one feel smart:

«Failure is not a prerequisite of success.» – I knew that

«Forgoing sleep is a bad ideia.» – I also knew that

«Other people’s failures are just that: other people’s failures» – Duhh

«Revenue in, expenses out, Turn a profit or wind up gone.» – Heck, even the tavern owner where i go for cheap drinks knows this

«If you want to get someones attention, it’s silly to do exactly the same thing as everyone else.» –  I rest my case

But in the other hand, you are always making reality checks, comparing your own practices with the ones described in the book, and this review is obviously good.

Anyway, the work smart not hard philosophy makes sense, there are some good marketing tips, i really liked the teach and spread your secrets of the trade approach. It also makes a strong point about minimalistic products, those products that you strip down to the core, make them easier, cheaper, maintainable. Likewise, they don’t like guys throwed in suits (about#hate) and useless meetings (about#hate) ….. ahhhh…that was good for my ego.

The final balance is positive, everyone can get good ideias out of it, but is not the “fabulous”, “best book in my life and afterlife” hype that you read in Amazon reviews.

Here some of my favorite quotes:

«When you treat people like children, you get children’s work»

«And when everything is high priority, nothing is»

«Business are usually paranoid and secretive. They think they have proprietary this and competitive advantage that. Maybe a rare few do, but most don’t»

«Having the idea for eBay has nothing to do with actually creating eBay»

«The worst interruptions of all are meetings»

«How long someone’s been doing it is overrated. What matters is how well they’ve been doing it»

Summer vacation video

Almost half year afterward, i finally had the mental space to review all those pics and videos that we brought back from the States. Anyway, to avoid loosing all those great moments in time – like tears in rain :), i did a video presentation with the finest pics and some videos.

The work horse was the excellent web based Animoto (some years ago a web based video tool this powerful would seemed science fiction) backed up by Virtualdub (and assorted filters) for video manipulation, Xvid codec, Lame MP3 codec, Audacity for the sound edition, RichFLV for FLV publishing, MediaInfo for getting all the needed information about several files. Except Animoto witch is a payed service, all the stuff is opensource or freeware so no costs added.

The final result? Priceless…

Oil change

My car odometer logs 260k kilometers plus some nickels and dimes, time was due to yet another oil change. This is the most simple maintenance operation you can do to your car, but is very common to be overlooked or overdone by car owners. It should be done according to your vehicle manual intervals and with the right oil specification and quantity. Its advisable to always change your oil filter with the oil, something that i don’t comply myself, because since the ECU reprogramming i shortened the oil change intervals (from 15k to 10k), and if the new oil is the same brand/viscosity of the old i keep the same filter between two oil changes (for 20k).

This is normally a cheap work to do at the shop, but i rather do this myself, because of several reasons:

  • This work is normally done by the workshop less qualified person
  • Sometimes they add too little or too much oil
  • Most places do this by suction through the oil level stick, so some impurities just remain at the carter
  • If done the correct way, through the carter, they have a tendency to over tight the drain screw (into the aluminum carter…)
  • I know for sure the stuff i am pouring in, witch is adequate (has the correct certification) for the engine, also i try to stick to the same brand and viscosity
  • Normally i take around 1h, witch is less than you usually wait at shop looking to some grease monkeys working at slow motion.
  • The economical savings (even if really not that much, they do add up with time)

So, first thing prior to get started, is to join everything you will need together:

  • Jack stands (well within your vehicle weight)
  • A jack
  • A red newspaper (ex: Expresso economy section)
  • Oil according to engine, for me its 4.2L of VW505.00 specs, normally BP 5000 (it depends on the promotional prices).
  • An oil catching pan
  • A rag (ex: old Lisbon Half Marathon t-shirt)
  • A wrench that works good on the drain screw (normally hex or monkey-wrench)
  • Some screwdrivers, Phillips, Torx, Flat, etc to remove the lower engine cover
  • Eye protection goggles
  • Funnel
  • Silicone joint tube (optional)
  • All purpose rubber compatible lubricant (optional)
  • Oil filter (not in pics cause i didn’t swapped it)
  • Oil filter wrench (not in pics cause i didn’t swapped the oil filter)

Lets put those hands to business, first and most important, park and break the car in a flat area (can be outside) and lift the front of your car and place it SECURELY on the jack stands. Do not improvise or try to be inventive in any way in this point, later if this goes bad and you happen to be under the car…. also don’t forget to put the eye protection goggles when working under the car, remember safety first.

Start to take the lower engine cover out with the appropriate screwdrivers . Afterward, locate the drain plug and cover the ground with the newspaper and place the oil catching pan, now remove the drain plug, and be careful so the the drain plug don’t fall to the pan…. let the old oil flush out, with the engine a bit warm the oil will flow much faster, so normally i do this half an hour after a drive.

While the oil is flushing you can get swap the oil filter, in the VW Golf is a cartridge that you can access from the top, with the filter wrench, there are some o-rings that you should also replace in the filter assembly when putting the new filter on.

Now, you should put the drain plug on again, many people always replace with a fresh one, but i only do this if it is getting old or threaded, if not i simply clean it of old sludge (with the rag) and put a tiny bit of silicone oil joint at the end, so it will seal nice and easy. Put it on, but DO NOT over tight, specially with an aluminium carter, it should be snug, just don’t over do it.

Now, its time to refill your engine with the new oil, use the correct quantity or put in small portions and check the oil level stick. About the oil, check the manual and make sure it has the needed specification for your engine and climate. The oil level should be between the max and min marker, not more nor less.

About the brand, there are tons and tons of information floating around, and everybody seems to have their own opinion or sympathy. My 2 cents, again don’t over do it,  use the manual specified oil of a major brand, and choose by some rational criteria, like price or by tech sheets (not just because someone told somebody that eared a person talking about a 1 zillion kilometers car that run only with oil xyz). If you want to upgrade the engine specified oil, fine, but i see so many folks pouring money in ultra high tech oils into some engines that would live happily with a 10W40…

At this point, usually i take advantage as the car is already in jack stands to lubricate the car silent blocks and rubber joints with an all purpose rubber compatible lubricant. Don’t forget to put the engine cover back in place and to clean everything, and to pack up your tools. Also put the old oil in the new oil container (now empty), and please do deliver it to recycling in a workshop (normally the same place where you bought the new oil).

Put the car back on the ground, get a cold beer and enjoy the next thousands kilometers.