As weather is taking a turn for worse, we are moving from Deep Water Bay over to Tai Tam Bay, which has more shelter - it did mean crossing some pretty rough seas. I enjoyed it hugely, but the ships cats (three of them) didn’t take it so well - so there’s a bit of a mess downstairs. Poor cats.

  So, I’m in Hong Kong. I was supposed to be going to Japan, but come Friday morning and as news of the Great East Japan earthquake was breaking on television it all suddenly seemed like maybe not such a good idea after all. A few calls to Trailfinders and I find myself booked on the next flight to Hong Kong instead, with a view to maybe

Because there’s nothing worse than carting a whole load of luggage around. I would exclude the netbook but it’s needed for work reasons, otherwise what you see is my entire baggage for 6 weeks across Hong Kong, Japan, New Zealand, Canada and USA. I’m considering ditching the blue towel, so it may get even lighter…

Quite often, when matching data sets, I come across the problem of what happens when data is “dirty” in someway. In other words, it’s become corrupted, either by a spelling mistake (such as HSBC becoming HSBBC) or by abbreviation (such as Gangle and Wotsit Limited becoming Gangle and Wotsit Ltd) or by contraction (such as Gangle and Wotsit Financial Adviser Ltd becoming Gangle and Wotsit Ltd). In these cases a direct comparison will return no match, so it’s good to have a way of returning data that is, roughly, approximately, a match.

In which I decided to take up Ryanair on one of their many sales, and travel to Norway and back. In one day. On a flight costing 2p. Return. It was fun - though slightly hectic. Up at 7am, back by midnight, about 11 hrs of travel and 6 hours in the scenic town of Sandefjord, Norway.

A bit of a long winded code segment this, it could probably be shortened significantly with a lookup table of titles (like Mr, Mrs, etc.), but this code is written with badly formatted databases in mind. In this case it takes a full name field (like “Mr John D Smith” for example) in table called tblNames and splits it out into different fields for title, first name, middle name and surname.

A useful little hack if you’ve installed support for DTS in SQL Server 2005 but also need a way of scheduling the packages. The solution I came up with (warning: not pretty) was to enable the xp_cmdshell system stored procedure and make an external call to the DTSrun program. Of course, you would be better off just calling it as a scheduled task from windows, but in case that’s not an option (for whatever administrative reasons), here’s the workaround…

There are many ways of doing this, mine is just one. I find this a quick method when you want an exact copy, and the database can be taken offline for a few moments. First, mark the source db as offline in the management console. Then, in windows explorer, make a copy of the data and log file. Put the source db back online. Rename the copies to your desired new database name.

I always have a preference for doing as much processing in SQL as possible. In this scenario, I needed to get the address out of a database in one line. The data was stored in multiple fields (one per line), some of which may be empty or null. create FUNCTION [dbo].[udf_concat_fields] (@add1 varchar(255), @add2 varchar(255)) RETURNS varchar(512) AS BEGIN declare @catadd varchar(512) set @catadd = case isnull(@add1, '') when '' then '' else @add1 + ', ' end + case isnull(@add2, '') when '' then '' else @add2 end if (right(@catadd, 2) = ', ') set @catadd = left(@catadd, len(@catadd)-1) --clean out unnecessary commas at end of the line return (@catadd) END The function can then be called recursively

If you want to get in touch, please use the form below. Don’t fill this out if you're human: Name Email address Message Send