Presenting Leading Zeroes in a Query

This little tip came in handy for me when sorting objects by their position within a tree structure.

If you build up a string such as 0.0.1.0 representing the nodes position within a tree structure then sorting will result in an ascii sort where 0.0.10.0 comes before 0.0.2.0 - not what you would want.

This can be resolved by simply pre-pending some leading zeroes to each number in the list, giving something like 0000.0000.0010.0000 which would give you the sort order you are after.

Anyway, enough of my chat, here is the code.

DECLARE @value bigint; SET @value = 10;
SELECT RIGHT('0000'+CONVERT(varchar(4),@value),4)

This will return '0010'.

Hope that is useful to someone out there - even if not for sorting tree nodes.