Padding strings in SQL Server 2008

Created 29 January 2013 13:12

There are often cases when you need to format a string or number into a string with a specified length. SQL Server 2008 doesn't have an inbuilt "PAD" function, but you can get around that with some String functions.

For example, you want to format an integer value into a 6-digit string, where the integer is of variable length, and any missing digits must be padded with a '0'. In SQL Management Studio, run the following script to generate some test data:

CREATE TABLE #temp (Id int null)

INSERT INTO #temp (id) VALUES (1)
INSERT INTO #temp (id) VALUES (2)
INSERT INTO #temp (id) VALUES (5)
INSERT INTO #temp (id) VALUES (47)
INSERT INTO #temp (id) VALUES (123)
INSERT INTO #temp (id) VALUES (54789)
INSERT INTO #temp (id) VALUES (154789)

You can now use a combination of LEFT / RIGHT, LTRIM and REPLICATE to pad your string with leading or trailing characters:

	Id AS OriginalValue,
	(SELECT RIGHT(REPLICATE('0', 6) + LTRIM(Id), 6)) AS PaddedLeft,
	(SELECT LEFT(LTRIM(Id) + REPLICATE('0', 6), 6)) AS PaddedRight
	FROM #temp

Running this will result in the following output:
Results grid

Editing Video Tags with taglib-sharp

Created 24 January 2013 20:12

I've been using XBMC at home, and I've been trying to find software to edit Genres on my movie files so I can sort them a little better. To my surprise, I couldn't find anything that seems to be able to edit tags on all the file types that I used. My next step was looking for libraries, and I was pleasantly surprised with taglib-sharp - available at

I installed using the Nuget Package Manager in Visual Studio, and it literally took me 2 minutes to figure out a basic example and it worked perfectly on a number of files I tried!

using (TagLib.File tagFile = TagLib.File.Create("D:\\Temp\\101 Dalmatians.avi"))
    tagFile.Tag.Genres = new string[] { "Animated", "Family" };

I love software that just works.

Reconciling client-side and server-side lookup values with T4 Templates

Created 11 January 2013 09:59

One of the challenges when creating web applications is the separation of logic between the client and the server. Most solutions will have the concept of "lookups" e.g. categories or some type of data that is static but necessary for the business logic. In terms of the server-side code, you will usually reference these using enums or constants, so they are not scattered throughout your solution in code.

However, you often will need to execute logic on the client using these same lookup values. This means you need to declare them again, which can result in the client and the server-side code going out of sync if the lookups change. One nice way to tackle this if you're using Visual Studio is with the use of T4 templates.


You have an "Animal" class declared in your C# project and you want some of the const values exposed on the client.

T4 Template

<#@ template language="C#" #>
<#@ output extension=".js" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.Xml" #>
<#@ assembly name="C:\Development\MyProject\bin\MyAssembly.dll" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="MyNamespace.Lookups" #>

var Lookups = (function() {
    var pub = {};
    pub.Animals = (function() {    
	var pub = {};
	pub.Cat = '<#=MyNamespace.Lookups.Animal.Cat#>';
	pub.Dog = '<#=MyNamespace.Lookups.Animal.Dog#>';
	return pub;

    return pub;

Using the Lookups

Once you've saved and run the T4 template, you will then have a .js file to include in your project. You can then write JavaScript as follows to use the lookup values:
alert('Test: ' + Lookups.Animals.Dog);