Ragged Sort

Sorting a list of values that mix numbers and text is a classic problem: alphabetically, 10 comes before 2. This scalar function takes a value and pads it with zeros so that when you sort the padded column, numeric values line up in true numeric order while non-numeric values are pushed to the end. It is designed to be used inside an ORDER BY clause rather than called on its own.

Example:

SELECT Value FROM MyList ORDER BY dbo.RaggedSort(Value)

CREATE FUNCTION [dbo].[RaggedSort]
(
	@String VarChar(50),
	@PadLen SmallInt = 10
)
RETURNS VarChar(50)
AS
BEGIN
	RETURN CASE WHEN ISNUMERIC(SUBSTRING(@String, 1, 1)) = 1 THEN
		Replace(Space(@PadLen - Len(@String)), ' ', '0') + @String
	ELSE --NOT NUMERIC
		@String + Replace(Space((@PadLen - 1) - Len(@String)), ' ', '0')
	END--CASE
END
GO