sql - Sorting nvarchar column containing alphanumeric values

Question

One of my columns in a SQL Server 2005 table contains alphanumeric values and I want to sort the rows as per the alphanumeric sorting. HOUSE_NO is a NVARCHAR column and it contains values like this:

 - 2/1 NAWOG
 - 1/1
 - 2/1 A
 - 1/2 A GOLCHA
 - 1
 - 2
 - SHASWAT KUTIR
 - 3 A
 - 11/1
 - 11

And I want them to be sorted as:

 - 1
 - 1/1
 - 1/2 A GOLCHA
 - 2
 - 2/1 A
 - 2/1  NAWAG
 - 3 A
 - 11
 - 11/1
 - SHASWAT KUTIR

I have tried many methods but not successful. I need your suggestions

Answer 1

Yuck. This is not something you're going to want to do in T-SQL against large data sets.

DECLARE @x TABLE(HOUSE_NO NVARCHAR(32));

INSERT @x SELECT '2/1 NAWOG'
UNION ALL SELECT '1/1'
UNION ALL SELECT '2/1 A'
UNION ALL SELECT '1/2 A GOLCHA'
UNION ALL SELECT '1'
UNION ALL SELECT '2'
UNION ALL SELECT 'SHASWAT KUTIR'
UNION ALL SELECT '3 A'
UNION ALL SELECT '11/1'
UNION ALL SELECT '11';

DECLARE @n NVARCHAR(10) = N'%[^0-9]%'

SELECT HOUSE_NO FROM @x
    ORDER BY CASE 
        WHEN HOUSE_NO LIKE N'[0-9]' + @n
        THEN CONVERT(INT, SUBSTRING(HOUSE_NO, 1, PATINDEX(@n, HOUSE_NO)-1))
        WHEN HOUSE_NO NOT LIKE @n THEN CONVERT(INT, HOUSE_NO)
        ELSE 2147483647 END,
        CASE WHEN HOUSE_NO NOT LIKE @n THEN NULL
        ELSE SUBSTRING(HOUSE_NO, PATINDEX(@n, HOUSE_NO), LEN(HOUSE_NO)) END;

What is the point of sorting house numbers in the way anyway?