在sql中排序NVARCHAR字段(Ordering an NVARCHAR field in sql)

在sql中排序NVARCHAR字段(Ordering an NVARCHAR field in sql)

我使用以下查询在SQL中命令字段

Select Distinct x From t where c=1 order by x asc

X是nvarchar列，但当前列具有以下值

无论如何命令值是这样的：

没有奖，1,2,3,4,5,6,7,8,9,10

I am using the below query to order a field in SQL

Select Distinct x From t where c=1 order by x asc

X is an nvarchar column but currently the column have the below values

Is there anyway to order the values to be like this:

No Prize,1,2,3,4,5,6,7,8,9,10

最满意答案

在sql server 2012+中，您可以使用try_cast ，但您必须通过以下方式将distinct更改为group by ：

SELECT x FROM t WHERE c=1 GROUP BY x ORDER BY Try_Cast(x as int)

对于旧版本，您可以使用union：

SELECT x FROM ( SELECT x, CAST(x as int) as sort FROM t WHERE c = 1 AND ISNUMERIC(x) = 1 UNION SELECT x, NULL FROM t WHERE c = 1 AND ISNUMERIC(x) = 0 ) as distinctX ORDER BY sort

In sql server 2012+ you could use try_cast, but you will have to change the distinct to a group by:

SELECT x FROM t WHERE c=1 GROUP BY x ORDER BY Try_Cast(x as int)

For older versions you can use union:

SELECT x FROM ( SELECT x, CAST(x as int) as sort FROM t WHERE c = 1 AND ISNUMERIC(x) = 1 UNION SELECT x, NULL FROM t WHERE c = 1 AND ISNUMERIC(x) = 0 ) as distinctX ORDER BY sort