SQL Server 中 NVARCHAR 列的 Order By 子句

发布于 2024-10-06 19:06:37 字数 182 浏览 6 评论 0原文

我需要对 nvarchar 列进行排序。问题是该列没有相同的条目模式，该列的某些行的示例是

如何按 order By 子句对该列进行排序？唯一令我宽慰的是该列仅包含数字和字母数字字符。

原文

I need to make the order by for the nvarchar column. The issue is this that the column doesn't have the same pattern of entry, example of some rows of the column are

How to sort this column by order By clause? The only relief I have is that this column contains only numeric and alpha numeric characters.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

血之狂魔 2024-10-13 19:06:37

ORDER BY
   RIGHT(REPLICATE(N' ', 500) + MyValue, 500)

所以

9
12
78F
T2R
TEF

变成

Which will sort正确

你不能依赖ISNUMERIC

ORDER BY
   RIGHT(REPLICATE(N' ', 500) + MyValue, 500)

9
12
78F
T2R
TEF

becomes

Which will sort correctly

You can't rely on ISNUMERIC

回复收藏 0 原文

执着的年纪 2024-10-13 19:06:37

select
    *
from
    table
order by
    CASE
       WHEN not column like '%[^0-9]%' THEN CONVERT(int,column)
       WHEN column like '[0-9]%' THEN CONVERT(int,SUBSTRING(column,1,PATINDEX('%[A-Z]%',column)-1))
    END,
    CASE
       WHEN not column like '%[^0-9]%' THEN NULL
       WHEN column like '[0-9]%' THEN SUBSTRING(column,PATINDEX('%[A-Z]%',column),9000)
       ELSE column
    END

select
    *
from
    table
order by
    CASE
       WHEN not column like '%[^0-9]%' THEN CONVERT(int,column)
       WHEN column like '[0-9]%' THEN CONVERT(int,SUBSTRING(column,1,PATINDEX('%[A-Z]%',column)-1))
    END,
    CASE
       WHEN not column like '%[^0-9]%' THEN NULL
       WHEN column like '[0-9]%' THEN SUBSTRING(column,PATINDEX('%[A-Z]%',column),9000)
       ELSE column
    END

回复收藏 0 原文

杀手六號 2024-10-13 19:06:37

我猜你的问题是它没有正确排序数字和文本。

本文介绍了一种方法：

如何对包含数字的 SQL Server 中的 VARCHAR 列进行排序？

来自文章：

select MyColumn
from MyTable
order by 
    case IsNumeric(MyColumn) 
        when 1 then Replicate(Char(0), 100 - Len(MyColumn)) + MyColumn
        else MyColumn
    end

I'm guessing that you issue is that its not sorting the numbers and text correctly.

This article explains one approach:

How do I sort a VARCHAR column in SQL server that contains numbers?

From the article:

select MyColumn
from MyTable
order by 
    case IsNumeric(MyColumn) 
        when 1 then Replicate(Char(0), 100 - Len(MyColumn)) + MyColumn
        else MyColumn
    end

回复收藏 0 原文

~没有更多了~