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 🙂
LEN => LENGTH
LEFT => SUBSTR
CHARINDEX => INSTR
(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 @pathStr AS NVARCHAR(255)
DECLARE @folderPathStr AS NVARCHAR(255)

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

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 * FROM
(
	SELECT 'me' AS val
	UNION ALL
	SELECT 'you' AS val
	UNION ALL
	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 road.cc, 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:
chain-whip-sjs-836
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:
chain-whip-sjs-12847
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 ipayroadtax.com, 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 pinvoke.net.

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.