Alphanumeric data sorting using sql query

Hi All,

Today I am representing alphanumeric sorting using sql query.
Sometimes we want to sort data like numeric first and after that all alphanumeric data but sql order by clause doesn’t support this functionality. So I have used replicate function for alphanumeric sorting.

Syntax of Replicate Function:
REPLICATE(character_expression, integer_expression)

character_expression
An alphanumeric expression of character data, or other data types that are implicitly convertible to nvarchar or ntext.
integer_expression
An expression that can be implicitly converted to int. If integer_expression is negative, a null string is returned.

Now comes to the main point how can we use this function in sql for alphanumeric sorting.

See below script

DECLARE @t TABLE(alphanumeric NVARCHAR(50))
INSERT INTO @t VALUES('1')
INSERT INTO @t VALUES('2')
INSERT INTO @t VALUES('1-s')
INSERT INTO @t VALUES('1-d')
INSERT INTO @t VALUES('1-n')
INSERT INTO @t VALUES('v')
INSERT INTO @t VALUES('m')
INSERT INTO @t VALUES('11')
INSERT INTO @t VALUES('12')
INSERT INTO @t VALUES('22')
INSERT INTO @t VALUES('23')

--SELECT alphanumeric FROM @t ORDER BY alphanumeric

SELECT alphanumeric FROM @t ORDER BY
CASE WHEN ISNUMERIC(alphanumeric) = 1 THEN RIGHT(REPLICATE('0',51) + alphanumeric, 50)
WHEN ISNUMERIC(alphanumeric) = 0 THEN LEFT(alphanumeric + REPLICATE('',51), 50)
ELSE alphanumeric
END

Here, I have defined alphanumeric column as NVARCHAR(50) so I have fixed it as following ways
1.    In case of numeric data, I have added 51 zeros in front of the column data and retrieve the last 50 character.
2.    In case of non numeric data, I have added 51 blank spaces at the end of the column data and retrieve the first 50 character.