Code

Let’s say you are migrating a Classic ASP site to .net core - because you really should! And let’s say you can’t migrate all the pages at once, but you want the new site to be up and running as soon as possible. If so, you are going to need to find a way to run those classic asp pages in the new site… A .net Core web site arranges all static content (mostly front end resources like css and js) in the wwwroot folder in the project directory.

Well now. It’s been a while. This site used to be hosted on a wordpress install on a shared hosting server. Then I let it sit for ages and did nothing with it while I got on with work. But I’ve wanted to bring it back to life and get my teeth into something a little more in line with my expertise, so it’s all moved. It’s now a JAM Stack site, built via Hugo and running on Netlify.

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.

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).

It seems like the last couple of months I’ve been doing nothing but data cleaning and transformations. As a result, I’ve been having to create all kinds of strange temporary functions to manipulate data to extract the required information. Particularly, date transformations. Of course, different installations of SQL Server (not to mention different database systems) handle dates in different ways - so it’s been important to handle them consistently. This little bit of T-SQL has been very useful in that regard…