排序包含字母数字值的nvarchar列 [英] Sorting nvarchar column containing alphanumeric values

问题描述

我在SQL Server 2005表中的一列包含字母数字值，我想按照字母数字排序对行进行排序. HOUSE_NO是NVARCHAR列，并且包含如下所示的值:

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

我希望将它们排序为:

 - 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

推荐答案

糟糕.对于大型数据集，这不是您要在T-SQL中要做的事情.

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?

这篇关于排序包含字母数字值的nvarchar列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！