Monthly Archives: December 2011

Oracle: Trim, replace, substring and indexof #oracle

Further to my post about SQL Server, here’s the differences I need to consider when scripting the same thing in Oracle:
REVERSE AND REPLACE are the same 🙂
(String concatenation) + => ||
PATINDEX => REGEXP_INSTR (which is more powerful out of the box, of course you can do regex with SQL Server, but not out of the box)

SQL Server: Trim, replace, substring and indexof #sql #sqlserver

I often have to deal with file paths in SQL, whilst at home in C# I always scratch my head when I need to do string manipulation in SQL, so here’s a quick outline (via an example) of the string manipulation functions I often use and frequently forget.

DECLARE @folderPathStr AS NVARCHAR(255)

SELECT @pathStr = 'C:\Temp\another dir\file%20name.ext'
--C:\Temp\another dir\file%20name.ext

SELECT @pathStr = REPLACE(@pathStr, '%20', '_')
-- @pathStr is now 'C:\Temp\another dir\file_name.ext'

-- substring 10 characters
SELECT @folderPathStr = LEFT(@pathStr, 10)
-- @folderPathStr is now 'C:\Temp\an'

-- But for folder path we want to take the filename off, so we need the last occurance of the backslash
-- CHARINDEX('\', @pathStr, 1) would return 3, which is the first occurance, if you want the last then reverse the string
-- to get the number of characters from the end, then take this away from the total
-- Note: CHARINDEX is 1 based, but passing 0 here would be fine too.
-- CHARINDEX('\', REVERSE(@pathStr), 1) would return 14
-- LEN(@pathStr) - CHARINDEX('\', REVERSE(@pathStr), 1) would return 19
-- So we can use this to do a substring of length 19, we do this via the LEFT function
SELECT @folderPathStr = LEFT(@pathStr, LEN(@pathStr) - CHARINDEX('\', REVERSE(@pathStr), 1))
-- @folderPathStr is now 'C:\Temp\another dir'

So to summarise, LEN, LEFT (also there is a RIGHT), REPLACE, REVERSE and CHARINDEX are in my normal armory.

Also, we must not forget PATINDEX, this is the pattern matching version of CHARINDEX, which you can use for endswith, startswith and contains:

SELECT * FROM docnames
WHERE PATINDEX('%.doc', val) > 0 -- All .doc files (endswith(.doc))
OR PATINDEX('me%', val) > 0 -- or filenames beginning me (startswith(me))
OR PATINDEX('%error%', val) > 0 -- or filenames contains error (contains(error))

SQL Server: LIKE clause syntax for reserved characters like percent % #sql #sqlserver

The most common thing I hit when trying to do a LIKE is trying to use the percent sign (%).
Fortunately the LIKE clause has a means of escaping the reserved characters, you simply stick a different letter in front of the reserved character then indicate that is the escape character, best to see this in an example:

	SELECT 'me' AS val
	SELECT 'you' AS val
	SELECT 'me%20and%20you' AS val
) vals
WHERE val LIKE '%/%%' ESCAPE '/'

Here I am escaping the percent sign with a forward slash and the result would be ‘me%20and%20you’.
If I didn’t then no matter how many percent signs I used there is no combination to actually filter only those rows that contain a percent sign.

More bicycle light beam patterns #cycling

I’m always drawn to bicycle light comparisons and beam patterns, mainly because you never know when your prized light will be lost, stolen or broken.

I liked this bicycle light beam comparison by, simple, yet good.

Cheap chain whip = false economy? #cycling

I am the kind of person who doesn’t use bike shops for maintaining my machine, I like the convenience (and saving money) of doing the maintenance I can at home, it’s a learning process at times, but given the right tools it’s pretty easy stuff. But the only thing more annoying than having to spend my hard-earned cash (no comments please) on bicycle tools I won’t use very often is spending cash for them to be used a couple of times before needing replacing.

This post is dedicated to cheap tools, some you can scrimp on, others are worth the investment. I mistakenly thought I could get by with a cheap chain whip:
Sure, it worked well the first time I removed my Rohloff sprocket but it came off fairly easily compared with subsequent removals and replacements. So much so that the second use and the sprocket wasn’t budging, I had the Rohloff sprocket removal tool in a vice and heaving on the chain whip trying to loosen the sprocket. The cheap chain whip just bent in my hands, pointless straightening it, no fixing it, so into the metal recycling it went leaving me with a sprocket I couldn’t remove until I’d bought a new chain whip.
I bit the bullet and splashed out on a top rate chain whip with a view of needing one for the rest of my life it’d be worth the investment and I wouldn’t regret it:
This Park Tool beast is in no way comparable to the cheap one, it is in every way better, stronger, longer and more comfortable to hold. It could easily be classed as a dangerous weapon as it is a big chunk of metal bar. Anyway, I tried the Park Tool SR2 and with much less effort the sprocket popped off from its seal and hub oil sprayed out under the vacuum it created. My pregnant wife was nearly sick at the stink of the Rohloff hub oil, it was a wonderful moment of success and comedy.

I will recommend the Park Tool SR2 to anyone who is interested, they don’t come cheap, but they are solid and I wouldn’t be without one now.

If you need the tools, buy them and invest where necessary.

I propose to scrap vehicle excise duty and increase fuel duty

I like sums, so let’s do a few based on my limited knowledge of the figures involved…
Average car mileage is 10K miles.
Average car vehicle excise duty is £150.
150 / 10000 = £0.015 duty per mile
Average modern car does 10 miles to the litre.
0.015 * 10 = £0.15 duty per litre.

15p per litre wouldn’t really impact the massive fuel pump price we have at the moment.
So I propose to scrap vehicle excise duty and increase fuel duty by 15p per litre.

This would have cost savings to government including
1. Wouldn’t need to police or prosecute people for no tax
2. Wouldn’t need to post the reminders / forms in the post i.e. delivery costs
3. Wouldn’t need to administer the scheme
4. Wouldn’t need to pay a cut to the post office for taking payment etc
5. Wouldn’t need to maintain a whole heap of webpages dedicated to it

Benefits to me
1. If I do less miles I pay less duty
2. Car drivers won’t be confusing the duty with road tax, so it will help equality on the roads between vehicles and bicycles
3…. there are more I am sure.

Benefits to the environment
1. Extra fuel costs makes people focus on doing less miles
2. Less paperwork and resources wasted

Small one-off costs
1. Closing the scheme
2. Changing the law
3. Retraining traffic police
4. A small number of job losses

Why did I write this post?
Lots of reasons, but mainly because I hate hearing “cyclists have no right to be on the road, they don’t pay to use the roads” come up as often as it does.

See also, a website dedicated to the misconception about the duty.

Shell32 Interop DLL Generation

As far as I’m aware you’ve got two choices when you want to call non .NET assemblies, DllImport or Interop.

For DllImport you’ll want to consult

But that DllImport syntax isn’t for everyone, some like to pretend we are working with .NET assemblies and want to use an Interop DLL, but how?

First you need to generate one using Tlbimp.exe (Type Library Importer)
For example
c:\Windows\System32>tlbimp shell32.dll /out:me.dll
Microsoft (R) .NET Framework Type Library to Assembly Converter 3.5.30729.1
Copyright (C) Microsoft Corporation. All rights reserved.
Type library imported to c:\Windows\System32\me.dll

Now just include the me.dll in your project (as a normal .NET assembly reference), stick a “using Shell32 = me;” / “Import Shell32 = me” in your source code and voila, Shell32.Folder etc is at your disposal.

Note: retrospectively thinking, don’t generate the dll into System32, you’re just asking for trouble.

To Ibuprofen or to not?

When I first re-started cycling I used to carry Ibuprofen with me on rides just incase I injured myself, it is a great and cheap drug.
Today I read a BBC article on it, Ibuprofen at 50 and unfortunately it’s pretty true, I don’t know my doctor and I buy Ibuprofen and self-medicate when I need to.

Fortunately I don’t visit my doctor often and even less now I’ve given up impact sports and focus on cycling only, in fact I don’t think I’ve been this healthy before. My feet are in great condition, no hard skin, no blisters or anything like that. I spend less money on cycling that any other sport I’ve played too. I haven’t used Ibuprofen for a long time now, so maybe people should replace Ibuprofen with cycling and give up the other sports as I did?

I gave up carrying Ibuprofen after a few months of cycling (once I was more confident) and I’ve not really fallen off since either, so I don’t think it is required, of course if I went on a tour I would carry some even though it can be bought anywhere nowadays. Note: Dropping the bike in a pile of mud and jumping off doesn’t count as falling off, it is a controlled dismount.

I’ve been putting it off, but the time to delve into PowerShell is upon me

I’ve been putting it off, but the time to delve into PowerShell is upon me (again).
Yes, I’ve of course looked at and used some very basic PowerShell before, but I ultimately ended up with a .vbs script as I found something similar to what I wanted and it was easy enough to change.
This time I have to start from scratch and PowerShell it is…

Fortunately there are lots of free resources online to help, I found this one:
And it looks promising.

First impressions are positive, but I still want to write C#, oh well.

Are you ready for winter?

Yesterday I cleaned the bike, chainset, sprocket and fitted a new chain. Here’s a picture taken at work on my lunch break (because it was dark when I finished and so I’ve dirtied it a little getting it here, but it still looks clean compared with normal):

The observant amongst you will have noticed the winter tyres too. Yes, as it’s also going to get more and more icy this week I’ve also fitted my winter tyres:

As you can see, the front rim is narrow and it is a bit of a pain to fit the tyre:

Everyone should ride winter tyres when the frosty season starts, I ride Schwalbe Marathon Winter tyres, I’m not sure if many other brands are widely available elsewhere, but in the UK there’s not much choice.

Availability of winter tyres is a bit random at times, they sell out pretty quickly when the slippy weather starts, but for comfort and safety I fit mine fairly early on and ride them until the weather is good again. Some people will get a season out of them, others will get several, I’m hoping to get three, but this will be number two, so let’s see how much harsh braking I do – this morning’s test doesn’t count 🙂

Currently I’m using KMC X8-93 as my chain of choice – for no particular reason other than it is supplied with a missing link and KMC have served me well thus far (definition = only breaking through neglect, I’m trying to avoid neglect now, so no more breakages since):

Chains really suffer in winter, the rain, filth and salt really eat through them, so do remember to look after your chain. I regularly check chain wear using a chain wear indicator, but generally ignoring 1% wear, only changing when the indicator won’t fit anymore (probably meaning 2% wear or more). Those with deraileurs need to replace at 1% wear 🙂

Clothing, everybody knows how important it is in winter. Everybody likes different things though, I don’t mind rain, but hate boil in the bag waterproof clothing – which is never waterproof anyway. So for me, waterproofs are an expensive waste of time. I do like windproof fabric though, my Gilet and winter jersey (which both have windproof fronts) are brilliant before the peak of winter. Once winter peak hits, it’s more about covering the legs and arms too. Until the freezing temperatures I still wear shorts and only have my arms covered by a base layer or jersey. Once it is freezing I wear warm unpadded tights with padded shorts underneath, that way I don’t need to change the tights everyday, but can change the shorts, hence I don’t need so many pairs of tights (which are expensive). I also wear a warm base layer and a windproof jacket, I carry my gilet and arm and leg warms in my bag as emergency clothing just incase. If it is -10 celsius like last year then I’ll wear an extra jersey or base layer under the jacket too. When I mention warm here I am specifically referring to Roubaix or Merino fabrics.
More on winter and clothing to follow I’m sure.