Typically when we require a number to be represented as a string we employ a CAST or CONVERT. Sometimes though we want to preserve the natural 'sortability' of the numbers after the conversion and this poses a problem as string '8' is "bigger" than string '10' when evaluated for sorting. Here's a little trick I just learned to tackle this:

SELECT STR(num, length)

This casts num to a varchar of length characters. If num has fewer digits than length, it is padded with the appropriate number of leading spaces. So in our example, STR(8, 2) yields the string ' 8' which is "smaller" than '10' so the natural sortability of the 'stringified' number is preserved.

Published under: General Technology Tips ·  ·  ·  ·