Posts

  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

Ok - lets play a game called “imagine” for a bit. Lets imagine you have a database called Postcodes with a table called tblPostcodes which has a list (a very big one) of all the postcodes in the UK with their corresponding eastings and northings. Now you can do some fun stuff like work out how far the addresses in your database are from a specified postcode… Lets setup some variables.

One of the (many) things that I do is various ad-hoc statistical reports on data in SQL databases, so I’m forever churning out weird little SQL routines and cursors with the hope of extracting meaning from data. Here’s a handy little number I put together just recently which counts the number of words in a field in a table. Words are counted individually (the delimiter inside a field is a space).